Highlight in the row range duplicate respects to previous row

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,362
Office Version
  1. 2010
Hello,

My data start in column "D" in the row "6"
I need to highlight row 7 duplicate respects to previous row 5, then row 8 duplicate respects to previous row 6

Sample image is attached in which show row 7 highlighted is respect respects to previous row 6.... and the row 8 highlighted is respect respects to previous row 7.

Please suggest any formula or VBA solution

X,1,11,1,11,1,21,2,12,1,21,2,12,1,21,2,X2,X,1X,1,11,1,X1,X,1
X,X,2X,2,12,1,X1,X,XX,X,1X,1,11,1,21,2,12,1,X1,X,2X,2,12,1,1
X,1,11,1,11,1,21,2,12,1,21,2,12,1,21,2,X2,X,1X,1,11,1,X1,X,1
2,1,11,1,X1,X,2X,2,12,1,X1,X,1X,1,11,1,21,2,22,2,12,1,11,1,1
1,X,1X,1,X1,X,1X,1,11,1,21,2,12,1,11,1,X1,X,XX,X,XX,X,1X,1,1
X,2,X2,X,XX,X,2X,2,X2,X,1X,1,11,1,21,2,12,1,11,1,11,1,11,1,2
X,1,11,1,21,2,12,1,11,1,11,1,21,2,12,1,11,1,X1,X,1X,1,X1,X,1
1,X,XX,X,2X,2,12,1,X1,X,1X,1,11,1,11,1,21,2,12,1,11,1,X1,X,1
1,1,11,1,21,2,X2,X,XX,X,XX,X,1X,1,X1,X,XX,X,1X,1,X1,X,XX,X,X
1,X,XX,X,1X,1,11,1,11,1,11,1,11,1,X1,X,1X,1,11,1,21,2,X2,X,1
X,1,X1,X,2X,2,12,1,21,2,X2,X,XX,X,XX,X,1X,1,11,1,11,1,X1,X,X
1,1,X1,X,1X,1,21,2,12,1,X1,X,1X,1,11,1,11,1,21,2,12,1,X1,X,1
1,1,11,1,11,1,11,1,X1,X,1X,1,11,1,11,1,21,2,22,2,X2,X,1X,1,1
X,1,11,1,X1,X,XX,X,1X,1,11,1,11,1,11,1,21,2,12,1,11,1,X1,X,X
1,1,11,1,11,1,21,2,12,1,X1,X,XX,X,1X,1,21,2,X2,X,1X,1,X1,X,2
1,1,11,1,21,2,22,2,12,1,11,1,X1,X,1X,1,21,2,12,1,11,1,X1,X,1
2,1,X1,X,1X,1,11,1,X1,X,1X,1,11,1,11,1,21,2,12,1,11,1,11,1,2
1,1,11,1,11,1,11,1,21,2,12,1,11,1,21,2,12,1,X1,X,XX,X,XX,X,1
X,1,11,1,21,2,12,1,11,1,21,2,12,1,11,1,X1,X,1X,1,11,1,11,1,X
1,1,11,1,11,1,X1,X,1X,1,11,1,11,1,11,1,11,1,11,1,11,1,11,1,1
X,1,11,1,11,1,11,1,21,2,12,1,11,1,X1,X,XX,X,1X,1,11,1,X1,X,1
1,2,X2,X,XX,X,XX,X,XX,X,1X,1,21,2,12,1,11,1,11,1,X1,X,1X,1,1
1,1,11,1,21,2,12,1,21,2,X2,X,XX,X,XX,X,XX,X,1X,1,X1,X,1X,1,X
1,1,11,1,21,2,22,2,12,1,X1,X,1X,1,X1,X,1X,1,21,2,12,1,X1,X,2
1,1,11,1,X1,X,1X,1,11,1,11,1,X1,X,1X,1,11,1,X1,X,1X,1,11,1,2
1,1,11,1,X1,X,1X,1,11,1,11,1,11,1,11,1,11,1,11,1,11,1,11,1,1
1,X,1X,1,11,1,21,2,12,1,11,1,X1,X,XX,X,XX,X,1X,1,X1,X,2X,2,1
2,2,12,1,11,1,X1,X,1X,1,X1,X,2X,2,12,1,21,2,22,2,12,1,X1,X,1

Thank you all.

I am using Excel 2000

Regards,
Moti
 

Attachments

  • Highlight Duplicate Rows.png
    Highlight Duplicate Rows.png
    29.8 KB · Views: 7

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
I need to highlight row 7 duplicate respects to previous row 5, then row 8 duplicate respects to previous row 6

Sample image is attached in which show row 7 highlighted is respect respects to previous row 6.... and the row 8 highlighted is respect respects to previous row 7.
Click to expand...

you have 2 different row reference for row 7 - you say based on row 5 and then row 6 !!!???

If its the previous row then a countif should work - if you are only highlighting a cell in Row 7 if there is a match anywhere in row6

A conditional format rule

Select D2 to O1000 - row based on how far down you want to go
then a formula
=COUNTIF($D1:$O1,D2)>0

So we are looking at cell D2 and then see if in Row1 D to O is that duplicated
Then move across E2, F2 etc and compare to Row 1 D to O
then move down row 3
we are looking at cell D3 and then see if in Row2 D to O is that duplicated
Then move across E3, F3 etc and compare to Row 2 D to O

Book6
DEFGHIJKLMNO
1X,1,11,1,11,1,21,2,12,1,21,2,12,1,21,2,X2,X,1X,1,11,1,X1,X,1
2X,X,2X,2,12,1,X1,X,XX,X,1X,1,11,1,21,2,12,1,X1,X,2X,2,12,1,1
3X,1,11,1,11,1,21,2,12,1,21,2,12,1,21,2,X2,X,1X,1,11,1,X1,X,1
42,1,11,1,X1,X,2X,2,12,1,X1,X,1X,1,11,1,21,2,22,2,12,1,11,1,1
51,X,1X,1,X1,X,1X,1,11,1,21,2,12,1,11,1,X1,X,XX,X,XX,X,1X,1,1
6X,2,X2,X,XX,X,2X,2,X2,X,1X,1,11,1,21,2,12,1,11,1,11,1,11,1,2
7X,1,11,1,21,2,12,1,11,1,11,1,21,2,12,1,11,1,X1,X,1X,1,X1,X,1
81,X,XX,X,2X,2,12,1,X1,X,1X,1,11,1,11,1,21,2,12,1,11,1,X1,X,1
91,1,11,1,21,2,X2,X,XX,X,XX,X,1X,1,X1,X,XX,X,1X,1,X1,X,XX,X,X
101,X,XX,X,1X,1,11,1,11,1,11,1,11,1,X1,X,1X,1,11,1,21,2,X2,X,1
11X,1,X1,X,2X,2,12,1,21,2,X2,X,XX,X,XX,X,1X,1,11,1,11,1,X1,X,X
121,1,X1,X,1X,1,21,2,12,1,X1,X,1X,1,11,1,11,1,21,2,12,1,X1,X,1
131,1,11,1,11,1,11,1,X1,X,1X,1,11,1,11,1,21,2,22,2,X2,X,1X,1,1
14X,1,11,1,X1,X,XX,X,1X,1,11,1,11,1,11,1,21,2,12,1,11,1,X1,X,X
151,1,11,1,11,1,21,2,12,1,X1,X,XX,X,1X,1,21,2,X2,X,1X,1,X1,X,2
161,1,11,1,21,2,22,2,12,1,11,1,X1,X,1X,1,21,2,12,1,11,1,X1,X,1
172,1,X1,X,1X,1,11,1,X1,X,1X,1,11,1,11,1,21,2,12,1,11,1,11,1,2
181,1,11,1,11,1,11,1,21,2,12,1,11,1,21,2,12,1,X1,X,XX,X,XX,X,1
19X,1,11,1,21,2,12,1,11,1,21,2,12,1,11,1,X1,X,1X,1,11,1,11,1,X
201,1,11,1,11,1,X1,X,1X,1,11,1,11,1,11,1,11,1,11,1,11,1,11,1,1
21X,1,11,1,11,1,11,1,21,2,12,1,11,1,X1,X,XX,X,1X,1,11,1,X1,X,1
221,2,X2,X,XX,X,XX,X,XX,X,1X,1,21,2,12,1,11,1,11,1,X1,X,1X,1,1
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:O100Expression=COUNTIF($D1:$O1,D2)>0textNO
 
Upvote 0
Solution
etaf said:
you have 2 different row reference for row 7 - you say based on row 5 and then row 6 !!!???

If its the previous row then a countif should work - if you are only highlighting a cell in Row 7 if there is a match anywhere in row6

A conditional format rule

Select D2 to O1000 - row based on how far down you want to go
then a formula
=COUNTIF($D1:$O1,D2)>0

So we are looking at cell D2 and then see if in Row1 D to O is that duplicated
Then move across E2, F2 etc and compare to Row 1 D to O
then move down row 3
we are looking at cell D3 and then see if in Row2 D to O is that duplicated
Then move across E3, F3 etc and compare to Row 2 D to O
Click to expand...
Hello etaf, I am sorry it is my typo error...I wanted for row 7 highlighted based on row 6 I tried the CF formula it worked spot on.

Thank you for you help and time you spent to solve it, Good Luck

Kind Regards,
Moti :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
427
AndyEd
A
M
  • Solved
Complicate highlight between 2 ranges (27 unique combinations Vs 12 results)
Replies
7
Views
309
motilulla
M
R
  • Solved
VBA Question: How to find duplicate rows, and sum values after finding duplicates?
2
Replies
14
Views
1K
Alex Blakenburg
A
K
  • Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
254
Eric W
Eric W
B
  • Question
dynamical Merging rows and adding up sum
2
Replies
11
Views
514
burkeam
B

Forum statistics

Threads
1,215,231
Messages
6,123,754
Members
449,119
Latest member
moudenourd

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top