Filter data from one collum by looking up from another column
If you want to exclude names, then try this:
=IF(ISERR(SMALL(IF(COUNTIF($A$1:$B$4,$A$1:$A$4)=1, ROW(INDIRECT("1:"&ROWS($B$1:$B$4)))),ROWS($1:1))), "",INDEX($A$1:$A$4,SMALL(IF(COUNTIF($A$1:$B$4,$A$1 :$A$4)=1,ROW(INDIRECT("1:"&ROWS($B$1:$B$4)))),ROWS ($1:1))))
ctrl+shift+enter (not just enter)
"mmaan" wrote:
Hi,
I am stuck on the following problem:
I want to exclude names (or give them another color) from 1 column if they
are also in another column.
To illustrate this, the result from the following data:
A B
1 John Peter
2 Sue Floyd
3 Peter Dan
4 Mike Chris
would have to be:
John
Sue
Mike
Or in this case I could use a conditional formatting to paint A3 red.
How do I do this?
|