Getting rid of not quite duplicate entries


E

el_grimley

Hello,

I've have got two lists of data. The first is a list of addresses with
each address having an address identifing number. The second is a list
of people at those addresses each name having the address indentifing
number to link them to the first list. There is usually more than one
person at each address but I want to limit this to one person at each
address for the purpose of eventually mail merging.

Advance filtering does not work as the name are different and so wil
not filter as unique records. Can anyone suggest how to cut down to one
person at each address?

Thanks in advance

Graham
 
Ad

Advertisements

B

Bob Phillips

Graham,

is the second list all in one column or in two. Can you give a sample set of
data?
 
E

el_grimley

Certainly

Sheet1
Address_ID, House Name, Address_1, Address2, Post_Code
001, The willows, Benfleet, Essex, SS8 7QH
002, Dunromin, Didsbury, Manchester, M9 7HG

Sheet2
Address_ID, Name, Mobile_number
001, Dave, 0321351531
001, Adam, 0035035035
002, Kitt, 2020505


With the end result looking like:
Sheet 3
Address_ID, House Name, Address_1, Address2, Post_Code, Name,
Mobile_number
001, The willows, Benfleet, Essex, SS8 7QH, Dave, 0321351531
002, Dunromin, Didsbury, Manchester, M9 7HG, Kitt, 2020505

Thanks again

Graham
 
B

Bob Phillips

Add these formulae to sheet3

B2: =VLOOKUP($A2,Sheet1!$A$1:$E$10,2,FALSE)
C2: =VLOOKUP($A2,Sheet1!$A$1:$E$10,3,FALSE)
D2: =VLOOKUP($A2,Sheet1!$A$1:$E$10,4,FALSE)
E2: =VLOOKUP($A2,Sheet1!$A$1:$E$10,5,FALSE)
F2: =VLOOKUP($A2,Sheet2!$A$1:$E$10,2,FALSE)
G2: =VLOOKUP($A2,Sheet2!$A$1:$E$10,2,FALSE)

and copy down.

Put the id in A2, and they will populate.

There are two entries for the id of 1 though which confuses, but you don't
display in the results?
 
Ad

Advertisements

E

el_grimley

Superb thank you very much. I woirked out something with vlookups bu
this works a little better.

Graha
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Getting Rid of Duplicate entries? 1
Get rid of Duplicates 1
Get Rid of Duplicate Cell? 3
How do I get rid of duplicate text? 1
getting rid of blanks 0
Get rid of the zeros?! 5
Get rid of #N/A 1
Get Rid of N/A 6

Top