Filter data from one collum by looking up from another column

[mmaan]

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?

 

[Ron Coderre]

Try something like this:

Using your posted example:

Select A1:A4, with A1 as the active cell

From the Excel main menu:
<format><conditional formatting>
Formula is: =COUNTIF($A$1:$B1,A1)>1
Click the [Format...] button and choose your settings
Click the [OK] buttons

Is that something you can work with?

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

XL2002, WinXP

 

[mmaan]

Hi Ron,

If I try it with my example data it works (if I replace the comma by a ";"),
but if I use it on my actual data (830 rows), it does not work.

It makes no sense to me.

Best regards,

Maarten

Try something like this:

Using your posted example:

Select A1:A4, Ron,

If I with A1 as the active cell

From the Excel main menu:
<format><conditional formatting>
Formula is: =COUNTIF($A$1:$B1,A1)>1
Click the [Format...] button and choose your settings
Click the [OK] buttons

Is that something you can work with?

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

XL2002, WinXP

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?

 

[mmaan]

Hi Ron,

I tried it once again, with a clean worksheet and now it works!

I am not sure what went wrong just now. But never mind that. Thank you for
your help!

Best regards,

Maarten

Hi Ron,

If I try it with my example data it works (if I replace the comma by a ";"),
but if I use it on my actual data (830 rows), it does not work.

It makes no sense to me.

Best regards,

Maarten

Try something like this:

Using your posted example:

Select A1:A4, Ron,

If I with A1 as the active cell

From the Excel main menu:
<format><conditional formatting>
Formula is: =COUNTIF($A$1:$B1,A1)>1
Click the [Format...] button and choose your settings
Click the [OK] buttons

Is that something you can work with?

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

XL2002, WinXP

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?

 

[Teethless mama]

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)