"intersect" 2 slightly-different worksheets

  • Thread starter Thread starter terry b
  • Start date Start date
T

terry b

Hello, All!
I think this 'problem' requires a pretty simple solution; I just don't
know what!
Say you've got 2 worksheets, one w/ 50 rows and 6 columns, all
relating to employee data (e.g., col headings like 'employee name',
'date of hire', 'hair color', and 3 other cols), and then a much longer
sheet, with 500 rows, and 7 columns relating to employee data--6 of
those cols being the same headings as the much-smaller sheet, but in
addition the larger "master" sheet also has a column called "employee
I.D. number", containing unique number-values.
Also: All the employees on small sheet are included w/in the large
one....
What I'm trying to do is: "Intersect" the employees' data from the
small sheet with the same records from the large sheet and include that
7th column w/ I.D. #'s, and form a 3rd sheet from that data. In other
words, the new/created worksheet would be identical to the original
small sheet, with the addition of the one additional column w/ the
correct/corresponding I.D. number.
Would I use some kind of filter to do this? or worksheet consolidation?
Thanks much in advance for your time & attention to detail [:-)
terry b.
 
Terry

If the data in the six columns on each sheet is absolutely identical, you
could set up a 'key' on the large sheet. (Insert a column at A and enter in
A2 say =B2&C2&D2&E2&F2&G2&H2 and copy down. This will give you something to
look up on that provides uniqueness). Then in column G on the third
worksheet (in G2) enter

=VLOOKUP(A2&B2&C2&D2&E2&F2,LargeSheetRange!$A$1:$H$500,8,FALSE)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Back
Top