A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
I have two lists of names eg.
List A
John A Smith
John B Smith
John C Smith
John D Smith
List B
John A Smith
John B Smith
John C Smith
John E Smith
As you can see, John D Smith is in List A but not B whereas John E Smith is in List B not A. I need something that will find these 'unique' people, i.e Not in BOTH lists.
Ive tried combining the lists and then sorting in Alphabetical order so it looks like:
John A Smith
John A Smith
John B Smith
John B Smith
John C Smith
John D Smith
John E Smith
But then I still don't know how to pull out the ones I need. Been doing it one by one with the two lists side by side but 3000 names is taking forever...
|
Brett
Premium Member
Registered: 16th Dec 02
Location: Manchester
User status: Offline
|
Combine lists like you said, then select that columns, then go on advanced filter and tick 'unique records only'
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
quote:
Originally posted by loafofbrett
Combine lists like you said, then select that columns, then go on advanced filter and tick 'unique records only'
Ive been trying that, I go in, check the box for unique records only with the one column highlighted, select copy to another location, click OK and it pastes a list of names that appear twice in the original list rather than only the ones that appear once. Tried it about 10 times.
I even put 123455 in a list and tried that and its just pasted 123455 in a new list rather than just 5. 
|
Brett
Premium Member
Registered: 16th Dec 02
Location: Manchester
User status: Offline
|
Well it wouldn't copy just 5 would it, it'd copy 12345 and omit the additional 5. Just tested it the same way and seems to work for me? My understanding was that you basically wanted to remove duplications?
[Edited on 09-06-2011 by loafofbrett]
|
Baskey
Member
Registered: 31st May 06
User status: Offline
|
If it's just unique records your after make sure your combined list is in column A (cell A1)
Then in B1 write the following formular
=countif(a:a,a1)
This will count how many times the entry in A1 is in the list. Drag the formular down and any where the count is '1' will be a unique record
[Edited on 09-06-2011 by Baskey]
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
quote:
Originally posted by loafofbrett
Well it wouldn't copy just 5 would it, it'd copy 12345 and omit the additional 5. Just tested it the same way and seems to work for me? My understanding was that you basically wanted to remove duplications?
[Edited on 09-06-2011 by loafofbrett]
No I want to extract the unique records, I need to make sure these people appear in both lists so need to identify who they are first.
|
A2H GO
Member
Registered: 14th Sep 04
Location: Stoke
User status: Offline
|
quote:
Originally posted by Baskey
If it's just unique records your after make sure your combined list is in column A (cell A1)
Then in B1 write the following formular
=countif(a:a,a1)
This will count how many times the entry in A1 is in the list. Drag the formular down and any where the count is '1' will be a unique record
[Edited on 09-06-2011 by Baskey]
This was my second option but again didn't know how to do it. That means I can then auto filter the 1's to get them all together 
|
Baskey
Member
Registered: 31st May 06
User status: Offline
|
You can indeed mate
|
