(Excel 2007) find contiguous duplicates? 1

[jkupov]

I know this should be easy... I want to find and highlight rows in which there is duplicate data in a specified column. The highlight duplicates does this well, but I am only concerned with duplicates which occur in contiguous rows. Is there a way to only highlight duplicate data in a column, which occurs only in contiguous rows? Thanks.

 

[SkipVought]

hi,

use a formula like
[tt]
Z2: =if(a1=a2,z1+1,1)
[/tt]
it identify contiguous duplicates.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jkupov]

If I am understanding this corrctly,you are putting the formula in cell z1 right? Wouldn't that create a circular error since you are referening z1 in the formula? Or am I missing something obvious?

so if I have the following, how would I use your formula to highlight the duplicates in D2 & D3?

A B C D E F
1 ## ## ## 123 ## ##

2 ## ## ## 456 ## ##

3 ## ## ## 456 ## ##

4 ## ## ## 789 ## ##

 

[SkipVought]

you are putting the formula in cell z1 right

[tt]
Z2: =if(a1=a2,z1+1,1)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jkupov]

Ah! *blushing* Thanks. I got it. I should have just tested before assuming! Thanks

 

[zelgar]

I don't know if the formula Z2: =if(a1=a2,z1+1,1) is correct. Using the example jkupov shown in his 2nd post the answers you's get in the Z column would be the follow:
z1 = would be blank or have a specific value inputed
z2 = 1
z3 = 2
z4 = 1

If the user wants a list of items with contiguous data, the formula would be z1&2:=if(a1=a2,1,0) and copy the formula from z2 down. Then having a conditional highlight on the z column if it equals 1

 

[SkipVought]

I assume proper spreadsheet design.

Row 1, NEVER CONTAINS DATA. Row 1 contains HEADING values

Data begins in row 2. Hence the formula is in ROW 2 and the formula, with regard to the posted example, would be...
[tt]
G2: =if(d1=d2,g1+1,1)
[/tt]
with results...
[tt]
1
1
2
1
[/tt]
the cf on column G cell value >1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

I think the OP needs to redefine what he wants. Does he want to highlight every row with duplicates (original and duplicates); or only the rows with the duplicates?

If the OP wants to highlight the original and duplicates, he needs to use my code; otherwise he needs to use Skip's code.

 

[SkipVought]

if both are to be highlighted, then
[tt]
G2: =IF(OR(D2=D1,D2=D3),1,0)
[/tt]
highligh all cells in column G =1


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

Why bother with the IF()?

=A1=A2

Returns a Boolean TRUE or FALSE.

 

[SkipVought]

good point
[tt]
ROW 2 formula: =or($d1=$d2,$d2=$d3)
[/tt]
could be used directly in the CF without a helper column.

All kinds of ways to skin a cat.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jkupov]

OP here... sorry for disappearing. The first response was enough to point me in the right direction. That's pretty much what I needed. Thanks for all the responses.