Name List Help

[clammy81]

I have two lists in columns A + B. All of the values of A are in B, however, B is unique from are. Essentially A is a subset of B.

I'm doing how I can identify unique values that do not come up twice.

For instance:

A (list)
Sam Hunt
Joe Smith


B (all names)
Sam Hunt
Joe Smith
Carrie Underwood
Mike Fisher

C (Unique names)
Carrie Underwood
Mike Fisher


I would like this to work so that Column C has all the unique values of B.

[Tony Valko]

In other words, you want the names from B that do not appear in A?

[Tony Valko]

Try this...

Data Range

	A	B	C
1	Some Names	All Names	Not in A
2	Sam Hunt	Sam Hunt	Carrie Underwood
3	Joe Smith	Joe Smith	Mike Fisher
4		Carrie Underwood
5		Mike Fisher
6

This array formula** entered in C2:

=IFERROR(INDEX(B:B,SMALL(IF(ISNA(MATCH(B$2:B$5,A$2:A$3,0)),ROW(B$2:B$5)),ROWS(C$2:C2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[clammy81]

Hmm it's not working..

New Approach..


Is there anyway to remove anything that appears twice on a list not just the "duplicate"

For Example:

John Smith
John Smith
Carrie Underwood
Mike Fisher
Mike Fisher
John Doe

I have a column about 600 rows x 1 column in length. I want to get rid of all the doubled values. I would just want the following below on my list. This is small scale however I want to apply it to a much larger list! Not just get rid of the duplicate

Carrie Underwood
John Doe

[Tony Valko]

Do you want to delete the entire row of data that contains duplicates?

[clammy81]

Yes, I want to delete anything that appears twice and keep anything that appears once.

Using the "duplicate" function would not help as I want to totally get rid of any name that pops up twice. These values are not unique to my study.

[jeffreybrown]

If you want to delete rows, then you could use this macro...

Three things:

1. List is in column A with a hearder row
2. Uses column B to filter the results (with a header row)
3. Deletes rows which have more than one result

Please Login or Register  to view this content.

Paste code in a Normal module

• Where to paste code
• Highlight macro to copy >> Ctrl + C >> Open your workbook
• Alt + F11 >> opens the Visual Basic Editor (VBE)
• Ctrl + R >>opens the Project Explorer (if not already open on left side of screen)
• Insert menu >> Module or Alt + I, M >> activates the Insert menu and inserts a Standard Module
• Paste code >> Ctrl + V (right side of screen)
• Alt + Q >> exits VBE and returns to Excel
• Back in Excel >> Alt + F8 >> Macro Dialog Box >> Highlight macro >> Run

NOTE: Please test on a copy of your data for testing purposes...

[Tony Valko]

Jeff's macro does it pretty much the same way I'd do it manually.

[newdoverman]

Here are a couple of array formulae that will do what you want. Enter with Ctrl + Shift + Enter

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Tony Valko]

=IFERROR(INDEX(B:B,SMALL(IF(ISNA($A$1:$A$2*$B$1:$B$4),ROW($B$1:$B$4)-MIN(ROW($B$1:$B$4))+1),ROWS($1:1))),"")

=IFERROR(INDEX(B:B,SMALL(IF(ISNA($A$1:$A$2*$B$1:$B$4),ROW($B$1:$B$4),""),ROW())),"")

Those will return incorrect results if the data is in a different order...

Data Range

	A	B
1	Sam Hunt	Carrie Underwood
2	Joe Smith	Mike Fisher
3		Sam Hunt
4		Joe Smith

[newdoverman]

@Tony
You're right. Its taking the last values only regardless of the order.

I don't know if I have a screwed up worksheet but I am getting incorrect results with your formula too...I hope that it is a screwed up worksheet as I don't see why it isn't working...it has been a long day.

	A	B	C
2	Sam Hunt	Joe Smith	Carrie Underwood
3	Joe Smith	Carrie Underwood	Sam Hunt
4		Sam Hunt	Mike Fisher
5		Mike Fisher

[newdoverman]

Here is one way to do this. Use column C as a helper column with this formula

Please Login or Register  to view this content.

Then in column D enter this array formula and fill down (Enter with Ctrl + Shift + Enter) All the values with 0 beside them will be returned.

Please Login or Register  to view this content.

	A	B	C	D
1	Sam Hunt	Joe Smith	1	Carrie Underwood
2	Joe Smith	Carrie Underwood	0	Mike Fisher
3		Sam Hunt	1
4		Mike Fisher	0

[Tony Valko]

Here's a small sample file that demonstrates this.

[newdoverman]

Nice one Tony! My worksheet must have been messed up when I tried it. It works like a charm.