Forum Discussion
Matching data from spreadsheets with uneven columns
mathetes Hi Thank you for your response. I will post a sample below. In the sample, I start with a sheet that contains 7 columns and 17 rows (Sheet A). The only column that has data is the Client ID column. I need to transfer the data from Sheet B to Sheet A, and also preserve the duplicates in Sheet A. Sheet B has 7 columns and 8 rows, no duplicates. What I have been doing is using a VLOOKUP formula (=VLOOKUP(A2,B.xlsx!$A$2:$B$8,2,FALSE) in each column in Sheet A to find the corresponding data in Sheet B. The problem is I have to type this formula in every column in Sheet A to get the data from Sheet B. In my actual report, there are more columns and rows, but my overall goal is to eliminate the repetition with VLOOKUP and speed up the process of transferring the data in Sheet A. Thanks!
SHEET A
| Client ID | Last Name | First Name | Warehouse Price | Q. Control | Sale Price | Approved Status? |
| 40921 | ||||||
| 40921 | ||||||
| 40921 | ||||||
| 40921 | ||||||
| 17664 | ||||||
| 17664 | ||||||
| 17664 | ||||||
| 40754 | ||||||
| 40754 | ||||||
| 79481 | ||||||
| 79481 | ||||||
| 79481 | ||||||
| 79481 | ||||||
| 98540 | ||||||
| 98540 | ||||||
| 98540 |
SHEET B
| Client ID | Last Name | First Name | Warehouse Price | Control | Sale Price | Approved Status? |
| 40921 | Withers | Bill | 50 | Passed | 100 | Approved |
| 17664 | Mertz | Ethel | 200 | Not Required | 500 | Not Approved |
| 40754 | Simmons | Bobby | 300 | Passed | 700 | Approved |
| 79481 | Tucks | Chris | 10 | Passed | 50 | Approved |
| 98540 | Dimond | Jaime | 1000 | Passed | 2500 | Approved |
| 99788 | Piazza | Mike | 20 | Not Required | 4000 | Approved |
| 24787 | John | Tommy | 800 | Passed | 400 | Not Approved |
Add a row above your Sheet A table with the numbers 2 through whatever
Let's say that's now row 1.
Then modify your formula so it uses the number in row 1 as the offset. Thus:
=VLOOKUP($A3,B.xlsx!$A$2:$B$8,B$1,FALSE)
When you copy that to all the rows and columns, the highlighted cell references will change appropriately. So you only need to write the formula once; after that it's copy and paste.