Have 2 columns of names - need to filter out names not duplicated

[Jill]

Hi I have two columns of names - Column A 1,377 names, Column G 1,456 names.
Most are duplicates. I want to see the names in another column that are not
duplicates. How do I use VLOOKUP to do this? Or do you have any other
suggestions?

 

[Ron Coderre]

Try this:

Using a list of 1,377 names in Col_A with a column heading in A1 and another
list of names in cells G2:G100.

A1: Name
C1: Name

H1: Test
H2: =ISNUMBER(MATCH(A2,$G$2:$G$100,0))

Select cells A1:A1377
Data>Filter>Advanced Filter
Check: Copy to another location
List Range: (already selected a1:a1377)
Criteria: (Select $H$1:$H$2)
Copy to: (Select $C$1)
Click the [OK] button

The list of matching names will be copied below Cell C1.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Jill]

Thanks Ron - I learned something new, however, when I did the filter, it gave
me the names of the persons who are in both columns. I want the names
filtered out that only appear once. In other words, I have two sets of
names. Most of the names are in both columns. I am trying to locate those
that appear in one or the other column. Thanks

Try this:

Using a list of 1,377 names in Col_A with a column heading in A1 and another
list of names in cells G2:G100.

A1: Name
C1: Name

H1: Test
H2: =ISNUMBER(MATCH(A2,$G$2:$G$100,0))

Select cells A1:A1377
Data>Filter>Advanced Filter
Check: Copy to another location
List Range: (already selected a1:a1377)
Criteria: (Select $H$1:$H$2)
Copy to: (Select $C$1)
Click the [OK] button

The list of matching names will be copied below Cell C1.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Hi I have two columns of names - Column A 1,377 names, Column G 1,456 names.
Most are duplicates. I want to see the names in another column that are not
duplicates. How do I use VLOOKUP to do this? Or do you have any other
suggestions?

 

[Ron Coderre]

Ahhhh...now I understand.

There may be other ways to do this, but this is my preference:

Assign range names to your 2 lists. I called mine rngList1 and rngList2.
Save your file.

(Note in the following steps, MS Query may display warnings about it's
ability to show the query ...ignore them and proceed.)

Then:
Data>Import External Data>New Database Query
Source type: Excel File
Source: Select your file
Select rngList1 and rngList2
Click [Next], accepting defaults until the next step.

At The last screen select The View data/Edit The Query option.
Click the [SQL] button

The initial SQL will look something like this:
SELECT rngList1.Name, rngList2.Name
FROM `C:\Excel Stuff\Lists`.rngList1 rngList1, `C:\Excel
Stuff\Lists`.rngList2 rngList2

Edit it to look like this:
SELECT Name FROM
(SELECT Name FROM `C:\Excel Stuff\Lists`.rngList1
UNION ALL
SELECT Name FROM `C:\Excel Stuff\Lists`.rngList2 )
GROUP BY Name
HAVING COUNT(Name) = 1

Click the [OK] button
(hopefully you'll see the unique values at this point)

Return the data to Excel.

The Unique values from each list should be displayed in the sheet.

Let me know if you have any issues/questions.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Thanks Ron - I learned something new, however, when I did the filter, it gave
me the names of the persons who are in both columns. I want the names
filtered out that only appear once. In other words, I have two sets of
names. Most of the names are in both columns. I am trying to locate those
that appear in one or the other column. Thanks

Try this:

Using a list of 1,377 names in Col_A with a column heading in A1 and another
list of names in cells G2:G100.

A1: Name
C1: Name

H1: Test
H2: =ISNUMBER(MATCH(A2,$G$2:$G$100,0))

Select cells A1:A1377
Data>Filter>Advanced Filter
Check: Copy to another location
List Range: (already selected a1:a1377)
Criteria: (Select $H$1:$H$2)
Copy to: (Select $C$1)
Click the [OK] button

The list of matching names will be copied below Cell C1.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Hi I have two columns of names - Column A 1,377 names, Column G 1,456 names.
Most are duplicates. I want to see the names in another column that are not
duplicates. How do I use VLOOKUP to do this? Or do you have any other
suggestions?

 

[wjohnson]

I would OPEN a New Worksheet and Copy and past ALL of column A Only -
then In column B - I would use some type of identifier and (i.e Column
A, or Sheet 1, etc.). Then Copy Column G or your second column and
paste it below your new column A and then in Column B - use some type
of identifier, to identify where the column came from.
Now sort Column A. Then add sometype of "Heading" into Cell A1.
Now enter the following formula into Cell C2:
=IF(A2=A1,"Duplicate","Not Duplicate"), This will give you a listing of
which cells are duplicate and not duplicate.
Column B - will tell you were the duplicates are. Then you can filter
for Duplicate or Not Duplicate - and create a new sheet.

 

[Ron Coderre]

For a more manual approach, try this:

Insert a blank sheet
A1: Name
B1: Unique?
Copy your Col_A list into the new sheet, beginning on cell A2.
Copy your Col_G list into the new sheet, immediately under the first list.

B2: =COUNTIF($A$1:$A$2834,A1)=1
Copy that formula down thru B2834

Then select A1:B2834
Data>Filter>AutoFilter
Click on the Unique? dropdown and select TRUE

There's your list of unique names.
(adjust range references if necessary)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

Thanks Ron - I learned something new, however, when I did the filter, it gave
me the names of the persons who are in both columns. I want the names
filtered out that only appear once. In other words, I have two sets of
names. Most of the names are in both columns. I am trying to locate those
that appear in one or the other column. Thanks

Try this:

Using a list of 1,377 names in Col_A with a column heading in A1 and another
list of names in cells G2:G100.

A1: Name
C1: Name

H1: Test
H2: =ISNUMBER(MATCH(A2,$G$2:$G$100,0))

Select cells A1:A1377
Data>Filter>Advanced Filter
Check: Copy to another location
List Range: (already selected a1:a1377)
Criteria: (Select $H$1:$H$2)
Copy to: (Select $C$1)
Click the [OK] button

The list of matching names will be copied below Cell C1.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Hi I have two columns of names - Column A 1,377 names, Column G 1,456 names.
Most are duplicates. I want to see the names in another column that are not
duplicates. How do I use VLOOKUP to do this? Or do you have any other
suggestions?

 

[Ron Coderre]

Darn typos!

The formula should be:
B2: =COUNTIF($A$1:$A$2834,A2)=1

NOT
B2: =COUNTIF($A$1:$A$2834,A1)=1

***********
Regards,
Ron

XL2002, WinXP-Pro

For a more manual approach, try this:

Insert a blank sheet
A1: Name
B1: Unique?
Copy your Col_A list into the new sheet, beginning on cell A2.
Copy your Col_G list into the new sheet, immediately under the first list.

B2: =COUNTIF($A$1:$A$2834,A1)=1
Copy that formula down thru B2834

Then select A1:B2834
Data>Filter>AutoFilter
Click on the Unique? dropdown and select TRUE

There's your list of unique names.
(adjust range references if necessary)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

Thanks Ron - I learned something new, however, when I did the filter, it gave
me the names of the persons who are in both columns. I want the names
filtered out that only appear once. In other words, I have two sets of
names. Most of the names are in both columns. I am trying to locate those
that appear in one or the other column. Thanks

Try this:

Using a list of 1,377 names in Col_A with a column heading in A1 and another
list of names in cells G2:G100.

A1: Name
C1: Name

H1: Test
H2: =ISNUMBER(MATCH(A2,$G$2:$G$100,0))

Select cells A1:A1377
Data>Filter>Advanced Filter
Check: Copy to another location
List Range: (already selected a1:a1377)
Criteria: (Select $H$1:$H$2)
Copy to: (Select $C$1)
Click the [OK] button

The list of matching names will be copied below Cell C1.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


:

Hi I have two columns of names - Column A 1,377 names, Column G 1,456 names.
Most are duplicates. I want to see the names in another column that are not
duplicates. How do I use VLOOKUP to do this? Or do you have any other
suggestions?