Trying to match 2 lists

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
585
I have 2 columns of numbers. Each column has some numbers unique to itself, but also some that exist in the other column. I would like to create a 3rd colum which "lines" up the first 2 columns on the values that are in common, and have a blank cell next to the unique values in the other column.

Is there a way to do this with cell formulas or is VBA the way to go?

thanks
Fred Emmerich
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
With data

1 2
2 4
3 5

What would your desired output be? It seems that inserting blank cells into columns A and B would be part of your problem. Or am I misinterpreting?
 
Upvote 0
All things being equal, I would use Microsoft Access to do this. It i much easier to do matching in Access than Excel (provided you know a little bit about Access). There are even unmatched query wizards to walk you through matching.

In Access, I would probably start by doing a Union Query between the two tables to get all possible values, then link each table to the Union query with a Left Outer join. You could then list all possible values, and indicate which ones are found in the first table and which ones are found in the second table (creating the structure you described).
 
Upvote 0
That is correct. It would need blank cells. That is my big issue-how to create those.


With data

1 2
2 4
3 5

What would your desired output be? It seems that inserting blank cells into columns A and B would be part of your problem. Or am I misinterpreting?
 
Upvote 0
I do what you suggest in Access a lot, but this is an Excel app (which others would use and might not have Access) and all my numbers are in the spreadsheet.


All things being equal, I would use Microsoft Access to do this. It i much easier to do matching in Access than Excel (provided you know a little bit about Access). There are even unmatched query wizards to walk you through matching.

In Access, I would probably start by doing a Union Query between the two tables to get all possible values, then link each table to the Union query with a Left Outer join. You could then list all possible values, and indicate which ones are found in the first table and which ones are found in the second table (creating the structure you described).
 
Upvote 0
You could emulate the same process in Excel, its just a little more work.

Combine both lists together and remove duplicates (let's say column A).
Then, use this list to do a lookup on your first list (column B).
And then a lookup on your second list (column C).
You can use IFERROR or ISNA logic with your VLOOKUP formulas to return empty strings instead of the #N/A errors you would normally get from VLOOKUP when there is not a match.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

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
Back
Top