highlight duplicate in single column after specific word

KRIXX

New Member
Joined
Oct 10, 2015
Messages
37
Office Version
  1. 2007
I am attaching excel workbook. after opening it you will see that in column A there are 162 records. In records you will notice that after some item numbers there is "Item" written. I want to use this word "Item" as separator of this records and highlights duplicate values which are begins after "Item" phase. In this attached reference sheet I have achieved result using condition formatting by selecting values between "Item" phase and then applied 'highlight duplicate values' But it is time consuming. Tthis data is for reference only. I have to dill will very vast data. So if you can provide me the formula which i can use in Conditional Formatting > New rule > Use a formula to determine which cells to format

 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Try the following

In the adjacent column (as an helper), place the following formula below (copy down). You can hide this column.
Code:
=COUNTIFS($A$2:A2,"Item")
In Conditional Formatting, set the following formula below.
Code:
=COUNTIFS($A$2:$A$163,"="&A2,$B$2:$B$163,"="&COUNTIFS($A$2:A2,"Item"))=2
 
Upvote 0
Try the following

In the adjacent column (as an helper), place the following formula below (copy down). You can hide this column.
Code:
=COUNTIFS($A$2:A2,"Item")
In Conditional Formatting, set the following formula below.
Code:
=COUNTIFS($A$2:$A$163,"="&A2,$B$2:$B$163,"="&COUNTIFS($A$2:A2,"Item"))=2
Sorry, but it's not working for me.
It will be very helpful if you can attach the file with formulas
 
Last edited:
Upvote 0
You would generally get faster & better help if you post your sample data/results directly in your post with XL2BB
Many of the helpers here choose not to download files from other sites or, due to security restrictions at work sites, are unable to download such files.

Sorry, but it's not working for me.
I think it certainly can work with a helper as navic suggested. Perhaps you did not implement it correctly. Here is a smaller sample with the same method though I have simplified the formulas a little. Note that the helper column and the CF are applied from row 2 down, not from row 1. The helper column could be hidden if you want.

I will make a separate post showing how you could do it without a helper column but I would probably stick with the helper column as the formulas are much simpler.

KRIXX.xlsx
AB
1Item
226090000340
326010001270
415010000310
5Item1
626090001491
736110000161
836110000171
936110000421
1036110000241
1136110000191
1226140000121
1336110000421
1426140000071
1526140000081
1615010000381
171020000311
1826010000491
19Item2
2026090001362
2126090000542
2226010001272
2336110000062
2436110000422
2526010001272
2626090000582
2726990000062
2815010000242
29Item3
3026140000033
3126140000023
3226140000123
3326140000083
3415010000243
35Item4
3626140000034
3726140000024
3826140000124
3936110000274
4036110000194
4126010001274
4220010000104
4313060000064
4413060000084
4526090000044
4626090000074
4726090000154
4826090000054
4936110000394
5036110000314
5126090000154
5222010000054
5315010000244
5415010000384
5526010000494
5636110000274
5726130000024
5826010001274
5926090000584
6026090001494
Helper
Cell Formulas
RangeFormula
B2:B60B2=COUNTIFS(A$2:A2,"Item")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A60Expression=COUNTIFS(A$2:A$60,A2,B$2:B$60,B2)>1textNO
 
Upvote 0
Without a helper - you see that the formula is considerably more complex.
Again the CF is applied from row 2.
Where I have used row $100 in the CF formula, you need to ensure that is a row somewhere below your last data.

KRIXX.xlsx
A
1Item
22609000034
32601000127
41501000031
5Item
62609000149
73611000016
83611000017
93611000042
103611000024
113611000019
122614000012
133611000042
142614000007
152614000008
161501000038
17102000031
182601000049
19Item
202609000136
212609000054
222601000127
233611000006
243611000042
252601000127
262609000058
272699000006
281501000024
29Item
302614000003
312614000002
322614000012
332614000008
341501000024
35Item
362614000003
372614000002
382614000012
393611000027
403611000019
412601000127
422001000010
431306000006
441306000008
452609000004
462609000007
472609000015
482609000005
493611000039
503611000031
512609000015
522201000005
531501000024
541501000038
552601000049
563611000027
572613000002
582601000127
592609000058
602609000149
Direct
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A60Expression=OR(MAX(IF(A$1:A1="Item",ROW(A$1:A1)))<MAX(IF(A$1:A1=A2,ROW(A$1:A1),0)),MIN(IF(NOT(ISNUMBER(A3:A$100)),ROW(A3:A$100)))>MIN(IF(A3:A$100=A2,ROW(A3:A$100),9^9)))textNO
 
Upvote 0
Solution
You're welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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