Finding Unique Values across 2 columns

[Wheelie686]

Column A has the complete list of names.
Column B has the names of people eligible for a prize.
Column C - I would like the remaining names from A - so those who would NOT be eligible for a prize.

Ideally I would like a formula for Column C instead of VBA.

[Aardigspook]

Put this in C2 and drag down:

Please Login or Register  to view this content.

You'll get a list with names and blanks - sort it to see just the names.
Does that help?

[pjwhitfield]

I think you need an array formula, put this in C1

Please Login or Register  to view this content.

Which needs to be entered using Ctrl+Shift+Enter then drag down

I am however a fraud when it comes to Array Functions and didnt come up with this solution myself but googled it, heres the explanation:
https://exceljet.net/formula/extract...ms-from-a-list

It should work though, by making the COUNT list both columns A & B then the winners will appear twice

[Wheelie686]

Thanks Aardigspook!
pjwhitfield - that actually gave me #N/A all the way down column C.

[Glenn Kennedy]

If you're still watching... I'd use a different way altogether:

=IFERROR(INDEX(A:A, SMALL(IF(ISERROR(MATCH($A$2:$A$11, $C$2:$C$6, 0)), (ROW($A$2:$A$11))), ROWS($E$1:E1))),"")

an array formula. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Aardigspook]

@Wheelie686
Glad you were happy with what I gave you and thanks for the rep - but I actually recommend you use Glenn's formula above, which removes the need to sort the column later.

@Glenn
I knew there would be an array formula to do without the blanks, but I couldn't get my brain in gear - thanks for jumping in.

[Glenn Kennedy]

...unless the data are so large....

But we don't know.