Matching 2 columns of data

Resolved · Urgent Priority · Version 2007

Wed 24 Jul 2013, 15:02 Delegate Mulin said...

Mulin has attended:
Excel Advanced course

Matching 2 columns of data

Hi,

I have 2 columns of names, 1 column containing names from Database A and another column containing names from Database B.

What I want to do is to match the names on both columns in a list view so I can see which names are missing from which Database.

I can send an example if required

Thanks

Mulin

Wed 24 Jul 2013, 21:09 Trainer Gary said...

RE: matching 2 columns of data

Hi Mulin

Thanks for getting in touch. You can achieve this with a VLOOKUP, and for best results combine it with an IFERROR.

Alongside the entries, in column C, assuming the data goes down to row 50 and starts in A1 (adjust as required):

=IFERROR(VLOOKUP(A1,$B$1:$B$50,1,0),"Missing in B")

In D you could have something very similar:

=IFERROR(VLOOKUP(B1,$A$1:$A$50,1,0),"Missing in A")

You would then have two extra columns. Where there is a match, the name will be repeated. Where there is no match, you will see the text "Missing in..." which you could then sort and filter on.

I hope this helps.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 1 Aug 2013, 13:14 Delegate Mulin said...

RE: matching 2 columns of data

Hi Gary,

Ok, I've tried this formula and it seems to work if the data in the rows in column a and b line up

For example in Column A i have 6000 entries, but in column B I have 5500 entries, 5500 entries match but 500 don't, and they are in order of A-Z

I have been doing things the long way round and inserting blank cells where there is missing data

Is there another formula i could use so that it reads the checks the whole of column B

All help appreciated

Thanks

Mulin

Thu 8 Aug 2013, 10:03 Delegate Mulin said...

RE: matching 2 columns of data

Hi , still waiting for reply on my question

Ok, I've tried this formula and it seems to work if the data in the rows in column a and b line up

For example in Column A i have 6000 entries, but in column B I have 5500 entries, 5500 entries match but 500 don't, and they are in order of A-Z

I have been doing things the long way round and inserting blank cells where there is missing data

Is there another formula i could use so that it reads the checks the whole of column B

All help appreciated

Thanks

Mulin

Read more: https://www.stl-training.co.uk/post-33947-matching--columns-data.html #ixzz2bMn84XpT

Fri 9 Aug 2013, 09:57 Trainer Gary said...

RE: matching 2 columns of data

Hi Mulin

Thanks for your reply. You should be able to do this with any size data. Modify this part of the formula:

$B$50

To the highest row you require. Any not in either list will have the "Not on column" message specified above.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 31 Jul 2013: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

The dual nature of toolbar buttons

Many toolbar buttons are dual purpose, though the two purposes are often linked in some way. For example, Align Left aligns a cell's contents to the left of the cell. However, hold down Shift and press the Align Left button: Excel aligns the cell contents to the right.
You may respond: So what? Well, you can reduce the number of buttons on your toolbar to make your screen less cluttered and allow more room for, perhaps, some of your own commands. After all, what's the point of an Align Right button when Shift+Align Left does the same thing?

View all Excel hints and tips