reporting_95
New Member
- Joined
- May 7, 2019
- Messages
- 30
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
Hello,
I have a report in which I start off only with the client IDs (Sheet A). I need to transfer data from Sheet B to Sheet A, but I must preserve the duplicate entries in Sheet A. What I've been doing is using VLOOKUP and referencing cell A2 in for every column in sheet A. This is okay but it can be very slow and repetitive. Also, I have to retype the formula in each column and make sure I count the correct number of columns in sheet B to obtain the desired data. I can't use autofill across the columns because the column index number changes from sheet B. I tried using Index Match formulas but I haven't been able to get it to work because the columns from both sheets are of different lengths. I have come across a few examples using Power Query, but I have to preserve the duplicate entries in Sheet A. I'm also a bit of a novice with using Power Query. Below is a stripped down sample of what my report and raw data can look like. Can someone please offer some suggestions on how to approach this task more efficiently? Thanks!
I have a report in which I start off only with the client IDs (Sheet A). I need to transfer data from Sheet B to Sheet A, but I must preserve the duplicate entries in Sheet A. What I've been doing is using VLOOKUP and referencing cell A2 in for every column in sheet A. This is okay but it can be very slow and repetitive. Also, I have to retype the formula in each column and make sure I count the correct number of columns in sheet B to obtain the desired data. I can't use autofill across the columns because the column index number changes from sheet B. I tried using Index Match formulas but I haven't been able to get it to work because the columns from both sheets are of different lengths. I have come across a few examples using Power Query, but I have to preserve the duplicate entries in Sheet A. I'm also a bit of a novice with using Power Query. Below is a stripped down sample of what my report and raw data can look like. Can someone please offer some suggestions on how to approach this task more efficiently? Thanks!
A.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Client ID | Last Name | First Name | Warehouse Price | Q. Control | Sale Price | Approved Status? | ||
2 | 40921 | ||||||||
3 | 40921 | ||||||||
4 | 40921 | ||||||||
5 | 40921 | ||||||||
6 | 17664 | ||||||||
7 | 17664 | ||||||||
8 | 17664 | ||||||||
9 | 40754 | ||||||||
10 | 40754 | ||||||||
11 | 79481 | ||||||||
12 | 79481 | ||||||||
13 | 79481 | ||||||||
14 | 79481 | ||||||||
15 | 98540 | ||||||||
16 | 98540 | ||||||||
17 | 98540 | ||||||||
Sheet1 |
B.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Client ID | Last Name | First Name | Warehouse Price | Q.Control | Sale Price | Approved Status? | ||
2 | 40921 | Withers | Bill | 50 | Passed | 100 | Approved | ||
3 | 17664 | Mertz | Ethel | 200 | Not Required | 500 | Not Approved | ||
4 | 40754 | Simmons | Bobby | 300 | Passed | 700 | Approved | ||
5 | 79481 | Tucks | Chris | 10 | Passed | 50 | Approved | ||
6 | 98540 | Dimond | Jaime | 1000 | Passed | 2500 | Approved | ||
7 | 99788 | Piazza | Mike | 20 | Not Required | 4000 | Approved | ||
8 | 24787 | John | Tommy | 800 | Passed | 400 | Not Approved | ||
Sheet B |