merging two sets of data

[spud]

I have one spreadsheet comprised of 20 individuals that are identified by a 5
digit number in column A and 10 columns of associated data related to these
individuals. I have another spreadsheet comprised of 1000 individuals
identified by a 5 digit number in column A and 20 columns of associated data
related to these individuals. The people in the first spreadsheet are
somewhere in the second spreadsheet. I need to bring over all 20 columns in
the second spreadsheet over to the first and match by the 5 digit number so
that the 20 individuals have the corresponding 30 columns. I know, long
winded..but hopefully it makes sense..is there a vlookup formula that will
bring over the entire 20 columns based on the 5 digit number?? Any help (sans
Vbasic) would be much appreciated...

 

[dlw]

copy the 20 below the 1000, sort the whole thing by the id number, so the ids
will be together. Then do a edit/find on each of the 20, cut and past the
10 columns to the end of the 20 columns and delete the 10 column row.
Sort of brute force, but in the end it may be faster.

 

[Art Farrell]

Hi spud,

Select cells L2 through AE2 . In the formula bar enter

=VLOOKUP(A2,Sheet2!$A$1:$U$1000,{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
19,20,21},FALSE)

Since this is an array formula use CTRL-SHIFT-ENTER to enter the formula in
all the selected cells. With all those cells selected have the cursor on the
lower right corner of AE2 and drag down for your remaining individuals. This
will transfer the information from Sheet2.

CHORDially,
Art Farrell