Help with replacing one column in Excel

[Progenix Oj101]

Hey guys, I need some assistance. Let me give you an idea of what I have, I think an example will be the easiest way to explain it:

Document 1
[table="width: 500, class: grid"]
[tr]
[td][/td]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[/tr]
[tr]
[td]1[/td]
[td]MD101[/td]
[td]219[/td]
[td]Description [MD101][/td]
[td]10500[/td]
[/tr]
[tr]
[td]2[/td]
[td]MD233[/td]
[td]449[/td]
[td]Description [MD233][/td]
[td]27.5[/td]
[/tr]
[tr]
[td]3[/td]
[td]TGD-4[/td]
[td]199[/td]
[td]Description [TGD-4][/td]
[td]160[/td]
[/tr]
[tr]
[td]4[/td]
[td]DD-55B[/td]
[td]999[/td]
[td]Description [DD-55B][/td]
[td]49[/td]
[/tr]
[/table]

Document 2
[table="width: 500, class: grid"]
[tr]
[td][/td]
[td]A[/td]
[td]B[/td]
[/tr]
[tr]
[td]1[/td]
[td]MD233[/td]
[td]489[/td]
[/tr]
[tr]
[td]2[/td]
[td]TDG-4[/td]
[td]199[/td]
[/tr]
[/table]

Document 3
[table="width: 500, class: grid"]
[tr]
[td][/td]
[td]A[/td]
[td]B[/td]
[/tr]
[tr]
[td]1[/td]
[td]MD101[/td]
[td]209[/td]
[/tr]
[tr]
[td]2[/td]
[td]DQS445BK[/td]
[td]549[/td]
[/tr]
[tr]
[td]3[/td]
[td]CCB1442EZ[/td]
[td]399[/td]
[/tr]
[/table]

What I need to happen is to be able to replace column B in Document 1 with column B in Documents 2, 3, etc where column A matches. Eg in Document 2, Cell A1 and A2 match Document 1's A2 and A3, and Document 3's A1 matches Document 1's A1.

• Document 1, Cell B1 must therefore be replaced by Document 3, Cell B1
• Document 1, Cell B2 must therefore be replaced by Document 2, Cell B1
• Document 1, Cell B3 must therefore be replaced by Document 2, Cell B2
• Document 1, Cell B4 doesn't have matches in any other documents and must either not be changed, or preferably set to 0
• Document 3, Cells A2 and A3 don't have matches in Document 1's column A and must be ignored
• Document 1, Columns A, C and D must not be changed EVER

Documents 2, 3, 4, 5... etc will always follow the same structure with only two columns each. Extra content in these documents must never impact Document 1.

Can anyone assist?

 

[patrick123]

The easiest solution apart from creating a macro would be the Vlookup command.
In DocumentA the 1st available column, Column F, you enter the formula:

=VLOOKUP(B5,[Document2]Sheet1!$B$4:$C$17,2,FALSE)

Next column would look along the lines of

=VLOOKUP(B5,[Document3]Sheet1!$B$5:$C$15,2,FALSE)

In the end you will get something like the attached image. Now simply copy the columns then Edit,Paste-special, Values; in-place, overwriting the vlookup formulas
Finally Replace the #N/A with zero and sum the rows of the columns, which you can now copy and paste the values into column B.

 

[Progenix Oj101]

The easiest solution apart from creating a macro would be the Vlookup command.
In DocumentA the 1st available column, Column F, you enter the formula:

=VLOOKUP(B5,[Document2]Sheet1!$B$4:$C$17,2,FALSE)

Next column would look along the lines of

=VLOOKUP(B5,[Document3]Sheet1!$B$5:$C$15,2,FALSE)

In the end you will get something like the attached image. Now simply copy the columns then Edit,Paste-special, Values; in-place, overwriting the vlookup formulas
Finally Replace the #N/A with zero and sum the rows of the columns, which you can now copy and paste the values into column B.

View attachment 301632

Thanks for that, I'll give it a try but I have a feeling it won't be automated enough, those tables are just examples which is why they are so small, the actual tables will contain several thousand entries each. I'll try later and see what happens

 

[patrick123]

Thanks for that, I'll give it a try but I have a feeling it won't be automated enough, those tables are just examples which is why they are so small, the actual tables will contain several thousand entries each. I'll try later and see what happens

Next level after that, still avoiding macros, is doing the lookup in hidden columns, with an IfError conditional to replace the #N/A that are returned and Column B = to the sum of that row. When updated sheets come simply copy and paste over the document2/document3, etc sheets & do a refresh. A couple of thousand Vlookups could slow you down a bit.

I think a macro might be your best solution.

 

[Progenix Oj101]

Next level after that, still avoiding macros, is doing the lookup in hidden columns, with an IfError conditional to replace the #N/A that are returned and Column B = to the sum of that row. When updated sheets come simply copy and paste over the document2/document3, etc sheets & do a refresh. A couple of thousand Vlookups could slow you down a bit.

I think a macro might be your best solution.

I'm completely open to a macro, but I'm also completely out of my depth