Match data from columns of different lengths

reporting_95

New Member
Joined
May 7, 2019
Messages
24
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. 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!

A.xlsx
ABCDEFG
1Client IDLast NameFirst NameWarehouse PriceQ. ControlSale PriceApproved Status?
240921
340921
440921
540921
617664
717664
817664
940754
1040754
1179481
1279481
1379481
1479481
1598540
1698540
1798540
Sheet1


B.xlsx
ABCDEFG
1Client IDLast NameFirst NameWarehouse PriceQ.ControlSale PriceApproved Status?
240921WithersBill50Passed100Approved
317664MertzEthel200Not Required500Not Approved
440754SimmonsBobby300Passed700Approved
579481TucksChris10Passed50Approved
698540DimondJaime1000Passed2500Approved
799788PiazzaMike20Not Required4000Approved
824787JohnTommy800Passed400Not Approved
Sheet B
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
145
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,722
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

Board Regular
Joined
Sep 22, 2019
Messages
145
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

reporting_95

New Member
Joined
May 7, 2019
Messages
24
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Sep 22, 2019
Messages
145
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 7, 2019
Messages
24
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top