Getting rid of not quite duplicate entries
 

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


--
el_grimley
------------------------------------------------------------------------
el_grimley's Profile: http://www.excelforum.com/member.php...o&userid=25924
View this thread: http://www.excelforum.com/showthread...hreadid=394153

Graham,

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

--
HTH

Bob Phillips

"el_grimley" wrote
in message ...

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


--
el_grimley
------------------------------------------------------------------------
el_grimley's Profile:
http://www.excelforum.com/member.php...o&userid=25924
View this thread: http://www.excelforum.com/showthread...hreadid=394153

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


--
el_grimley
------------------------------------------------------------------------
el_grimley's Profile: http://www.excelforum.com/member.php...o&userid=25924
View this thread: http://www.excelforum.com/showthread...hreadid=394153

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?

--
HTH

Bob Phillips

"el_grimley" wrote
in message ...

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


--
el_grimley
------------------------------------------------------------------------
el_grimley's Profile:
http://www.excelforum.com/member.php...o&userid=25924
View this thread: http://www.excelforum.com/showthread...hreadid=394153

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

Graham


--
el_grimley
------------------------------------------------------------------------
el_grimley's Profile: http://www.excelforum.com/member.php...o&userid=25924
View this thread: http://www.excelforum.com/showthread...hreadid=394153