unifying two sheets by a common index column

[Guest]

Hey all.

suppose i have two excel sheets, and they both got a common column that acts
as an index column (e.g. s/n of parts). the sheets have a different number of
values for each index num. and have different columns.

is there a way to created a unified sheet which will contain both sheets
columns, matched by the index column?

thanks!

 

[broro183]

Hi Yaron,

Yes, this can be done using a vlookup function eg:

=VLOOKUP('Initial Sheet'!$A1,'Other Sheet'!$A:$G,COLUMN('Initial
Sheet'!G1)-5,FALSE)

Copy this into the first empty column (cell G1 of "Initial Sheet" in my
example) of one of your two sheets & paste across for as many columns as
there are columns in the "Other Sheet" (cols A to G in my example) &
down as many rows as needed.

This part of the formula, "COLUMN('Initial Sheet'!G1)-5", means that
you don't have to change the formula to lookup a new column when you
copy it across the columns of the Initial Sheet.
If the data in your initial sheet goes to a different column, change
the "5" to another number so that the result is of this is 2 in your
first column.

This is based on the assumption that your "index column" is in column A
of both sheets.
Once all the data has been transferred into the Initial Sheet I would
select all the vlookup equations and copy & paste special as values
because a lot of vlookups can slow the spreadsheet down lots & I'm
guessing that once you have all the values in one sheet, the other one
won't be needed?

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[David McRitchie]

Hi Yaron
The safest way is probably to use VLOOKUP Worksheet Formula
http://www.mvps.org/dmcritchie/excel/vlookup.htm
assuming there are no both sheets have all of the index items.

 

[Guest]

wonderful, thank you.

 

[broro183]

hi Yaron,
Pleased I could help, thanks for the feedback.

Rob Brockett
NZ
Always learning & the best way to learn is to experience...