Trying to match 2 lists

[Mr930]

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

 

[texasalynn]

a vlookup would work within an if statement

 

[mikerickson]

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?

 

[Joe4]

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).

 

[Mr930]

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?

 

[Mr930]

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).

 

[Joe4]

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.