ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   How can I highlight duplicates from 2 rows using set criteria (https://www.excelbanter.com/excel-discussion-misc-queries/255323-how-can-i-highlight-duplicates-2-rows-using-set-criteria.html)

Sam.D

How can I highlight duplicates from 2 rows using set criteria
 
Basically I have a list of people from A2:A1475 who report into people in
N2:N1475.

I need to highlight everyone in column A who is duplicated in column N but
do not report into themselves, so when row A and N don't match.

I'm guessing I have to use conditional formatting but I really need help
with it.

Many thanks in advance

Sam.D


Max

How can I highlight duplicates from 2 rows using set criteria
 
Maybe this suffices
Select A2:A1475 (A2 active), then apply CF using Formula Is:
=COUNTIF($N:$N,A2)
Format to taste, ok out
--
Max
Singapore
---
"Sam.D" wrote:
Basically I have a list of people from A2:A1475 who report into people in
N2:N1475.

I need to highlight everyone in column A who is duplicated in column N but
do not report into themselves, so when row A and N don't match.

I'm guessing I have to use conditional formatting but I really need help
with it.

Many thanks in advance

Sam.D


Sam.D

How can I highlight duplicates from 2 rows using set criteria
 
Thanks,

Unfortunately this highlights all duplicates. I only need the duplicates
highlighted when they do not report directly into themselves as in when what
is in row A does not match row N.

Sorry for my vague explanation.

Sam.D


"Max" wrote:

Maybe this suffices
Select A2:A1475 (A2 active), then apply CF using Formula Is:
=COUNTIF($N:$N,A2)
Format to taste, ok out
--
Max
Singapore
---
"Sam.D" wrote:
Basically I have a list of people from A2:A1475 who report into people in
N2:N1475.

I need to highlight everyone in column A who is duplicated in column N but
do not report into themselves, so when row A and N don't match.

I'm guessing I have to use conditional formatting but I really need help
with it.

Many thanks in advance

Sam.D


Max

How can I highlight duplicates from 2 rows using set criteria
 
Try this as the CF formula for the dual condition:
=AND(COUNTIF($N:$N,A2),A2<N2)
Success? celebrate it, hit the YES below
--
Max
Singapore
---
"Sam.D" wrote:
Thanks,

Unfortunately this highlights all duplicates. I only need the duplicates
highlighted when they do not report directly into themselves as in when what
is in row A does not match row N.

Sorry for my vague explanation.

Sam.D



Sam.D

How can I highlight duplicates from 2 rows using set criteria
 
No Success I'm afraid.

Still has the same results.

Can I use formatting on both conditions?

Sam.D

"Max" wrote:

Try this as the CF formula for the dual condition:
=AND(COUNTIF($N:$N,A2),A2<N2)
Success? celebrate it, hit the YES below
--
Max
Singapore
---
"Sam.D" wrote:
Thanks,

Unfortunately this highlights all duplicates. I only need the duplicates
highlighted when they do not report directly into themselves as in when what
is in row A does not match row N.

Sorry for my vague explanation.

Sam.D



Max

How can I highlight duplicates from 2 rows using set criteria
 
Lost you there, I'm afraid. It should have worked. Maybe you could post some
sample/representative data from the 2 cols. Indicate exactly which data are
to be highlighted by the CF.
--
Max
Singapore
----
"Sam.D" wrote in message
...
No Success I'm afraid.
Still has the same results.
Can I use formatting on both conditions?




Sam.D

How can I highlight duplicates from 2 rows using set criteria
 
Please excuse me, it does work perfectly.

Many thanks for your help

Sam.D

"Max" wrote:

Lost you there, I'm afraid. It should have worked. Maybe you could post some
sample/representative data from the 2 cols. Indicate exactly which data are
to be highlighted by the CF.
--
Max
Singapore
----
"Sam.D" wrote in message
...
No Success I'm afraid.
Still has the same results.
Can I use formatting on both conditions?



.


Max

How can I highlight duplicates from 2 rows using set criteria
 
Glad to hear. You're welcome.
--
Max
Singapore
"Sam.D" wrote in message
...
Please excuse me, it does work perfectly
Many thanks for your help
Sam.D





All times are GMT +1. The time now is 10:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com