Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 4th 10, 01:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 9
Default 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


  #2   Report Post  
Old February 4th 10, 02:23 PM posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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

  #3   Report Post  
Old February 4th 10, 03:13 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 9
Default 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

  #4   Report Post  
Old February 5th 10, 12:21 AM posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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


  #5   Report Post  
Old February 5th 10, 02:01 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 9
Default 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




  #6   Report Post  
Old February 5th 10, 05:47 PM posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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?



  #7   Report Post  
Old February 10th 10, 11:34 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 9
Default 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?



.

  #8   Report Post  
Old February 10th 10, 02:36 PM posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Highlight rows if duplicates are found Pierre Excel Worksheet Functions 3 January 21st 10 04:03 PM
Highlight Duplicates with different Color samangh1 via OfficeKB.com Excel Discussion (Misc queries) 0 January 22nd 09 09:28 PM
Deleting rows by specified criteria for duplicates Tasha Excel Discussion (Misc queries) 4 September 9th 08 10:48 AM
Highlight Duplicates, Macro? NPell Excel Worksheet Functions 6 April 9th 08 02:02 PM
How do i locate/highlight duplicates ? Lizardking Excel Discussion (Misc queries) 3 October 12th 06 04:36 PM


All times are GMT +1. The time now is 01:12 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017