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