Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
merging two sets of data
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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
merging two sets of data
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. "spud" wrote: 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... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
merging two sets of data
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,1 0,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 "spud" wrote in message ... 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
show 3 sets of data per data point in a scatter plot | Charts and Charting in Excel | |||
Merging 2 sets of data | Excel Worksheet Functions | |||
how do i link up 2 sets of data into 1 set of data in excel | Excel Worksheet Functions | |||
help comparing two sets od data to find the odd data | Excel Worksheet Functions | |||
Two sets of data on one chart | Excel Discussion (Misc queries) |