software

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!


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 quot;Initial Sheetquot; in my
example) of one of your two sheets amp; paste across for as many columns as
there are columns in the quot;Other Sheetquot; (cols A to G in my example) amp;
down as many rows as needed.

This part of the formula, quot;COLUMN('Initial Sheet'!G1)-5quot;, 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 quot;5quot; to another number so that the result is of this is 2 in your
first column.

This is based on the assumption that your quot;index columnquot; 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 amp; paste special as values
because a lot of vlookups can slow the spreadsheet down lots amp; 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 amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=528932Hi Yaron
The safest way is probably to use VLOOKUP Worksheet Formula
www.mvps.org/dmcritchie/excel/vlookup.htm
assuming there are no both sheets have all of the index items.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;Yaron Assaquot; gt; wrote in message ...
gt; Hey all.
gt;
gt; suppose i have two excel sheets, and they both got a common column that acts
gt; as an index column (e.g. s/n of parts). the sheets have a different number of
gt; values for each index num. and have different columns.
gt;
gt; is there a way to created a unified sheet which will contain both sheets
gt; columns, matched by the index column?
gt;
gt; thanks!
wonderful, thank you.

quot;broro183quot; wrote:

gt;
gt; Hi Yaron,
gt;
gt; Yes, this can be done using a vlookup function eg:
gt;
gt; =VLOOKUP('Initial Sheet'!$A1,'Other Sheet'!$A:$G,COLUMN('Initial
gt; Sheet'!G1)-5,FALSE)
gt;
gt; Copy this into the first empty column (cell G1 of quot;Initial Sheetquot; in my
gt; example) of one of your two sheets amp; paste across for as many columns as
gt; there are columns in the quot;Other Sheetquot; (cols A to G in my example) amp;
gt; down as many rows as needed.
gt;
gt; This part of the formula, quot;COLUMN('Initial Sheet'!G1)-5quot;, means that
gt; you don't have to change the formula to lookup a new column when you
gt; copy it across the columns of the Initial Sheet.
gt; If the data in your initial sheet goes to a different column, change
gt; the quot;5quot; to another number so that the result is of this is 2 in your
gt; first column.
gt;
gt; This is based on the assumption that your quot;index columnquot; is in column A
gt; of both sheets.
gt; Once all the data has been transferred into the Initial Sheet I would
gt; select all the vlookup equations and copy amp; paste special as values
gt; because a lot of vlookups can slow the spreadsheet down lots amp; I'm
gt; guessing that once you have all the values in one sheet, the other one
gt; won't be needed?
gt;
gt; hth
gt; Rob Brockett
gt; NZ
gt; Always learning amp; the best way to learn is to experience...
gt;
gt;
gt; --
gt; broro183
gt; ------------------------------------------------------------------------
gt; broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
gt; View this thread: www.excelforum.com/showthread...hreadid=528932
gt;
gt;


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

Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=528932