Match data from columns of different lengths

[reporting_95]

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!

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

 

[denzo36]

Hi there,

So basically you are trying to look up the values in sheet B and bring them into sheet A and anytime there's a duplicate in sheet A you want that completed as well?

 

[Trebor76]

Hi there,

As the layout of both sheets are the same, put this formula...

=VLOOKUP($A2,B!$A:$G,COLUMN(),FALSE)

...into cell B2 of sheet A and copy across and down as required.

Note VLOOKUP will return the first match it finds. Subsequent matches are ignored.

Regards,

Robert

 

[denzo36]

Yes VLOOKUP is no good when doing matches with duplicates but Power Query is the easiest way to do it and will update when you tables change when you refresh the query.

It's really not that difficult, this article explains it really well: Merge Tables in Excel Using Power Query (Easy Step-by-Step Guide)

Basically you need to do the following:

• Change both your ranges to Excel tables
• Separately, import table 1 and table 2 into Power Query and only create a connection for each (article explains this)
• When both have been imported, you want to Merge your queries together (essentially merge the two tables)
• You want to join the queries on the column called Client ID as this is the common field in both tables
• You want to left join table one to table 2

Have a go and see how it goes and if you are having problems, revert back

 

[reporting_95]

Yes VLOOKUP is no good when doing matches with duplicates but Power Query is the easiest way to do it and will update when you tables change when you refresh the query.

It's really not that difficult, this article explains it really well: Merge Tables in Excel Using Power Query (Easy Step-by-Step Guide)

Basically you need to do the following:

• Change both your ranges to Excel tables
• Separately, import table 1 and table 2 into Power Query and only create a connection for each (article explains this)
• When both have been imported, you want to Merge your queries together (essentially merge the two tables)
• You want to join the queries on the column called Client ID as this is the common field in both tables
• You want to left join table one to table 2

Have a go and see how it goes and if you are having problems, revert back

Hi, Thanks for the info! I tried it out and it looks like it does work. I have a question though. On my original report I sometimes have client IDs that do not appear on the sheet from which I get the data (Sheet B). This is because those clients IDs are either new or don't have a matching criteria in the data sheet. When I'd use VLOOKUP, those rows would appear with a #N/A error. That's okay, because it indicates to me I should check those ID numbers against my external database. Do you know if in Power Query, if there a client ID in Sheet A does not appear in Sheet B, does it eliminate it or does it appear as a #N/A error?

 

[denzo36]

These should show up as NULL values - as your left joining Sheet A to Sheet B, what that does is keep all the values from Sheet A and only bring back those in matches in Sheet B. If there no matches for some entries in Sheet A on Sheet B it will show as a NULL value.

If you wanted to just show those in Sheet A that have match in Sheet B and eliminate the ones where there is no match, then you should you an inner join when merging your queries together.

 

[reporting_95]

These should show up as NULL values - as your left joining Sheet A to Sheet B, what that does is keep all the values from Sheet A and only bring back those in matches in Sheet B. If there no matches for some entries in Sheet A on Sheet B it will show as a NULL value.

If you wanted to just show those in Sheet A that have match in Sheet B and eliminate the ones where there is no match, then you should you an inner join when merging your queries together.

Thanks so much! I will keep using this method and circle back if I experience any problems.

 

[denzo36]

No problem, glad to help!