Identical Cells Highlighting

[Quimerreya]

So, here's the thing, I have somewhere around 600,000 cells with different numbers.

My English ain't perfect so I'll give a little example here.

Let's say

2
44
52
32
12
3 < Has to be highlighted
3 < Has to be highlighted
45
23 < Has to be highlighted
23 < Has to be highlighted
56
54

This is what I want to do. I want to make cells that are next to each-other(ONLY) with identical numbers to be highlighted.

I'd really appreciate some help. Thank you very much.

[Pete_UK]

Assuming your data starts in A1, select all the cells from A2 to the bottom (i.e. do not include the first cell), then click on Conditional Formatting | New Rule | Use a formula ..., then put this formula in the box:

=AND(A2=A3,A2<>"")

then click the Format button, choose the Fill tab, then choose your colour.

You need to repeat this, but with this formula:

=AND(A2=A1,A2<>"")

and click Format again to set your colour. Then OK your way out.

Hope this helps.

Pete

[Quimerreya]

Hello Pete,

This has been very helpful, works perfectly.

Thank you very much kind sir.

[Pete_UK]

You're welcome - thanks for feeding back.

Perhaps you can mark the thread as Solved if you consider it to be so.

Pete

[Quimerreya]

I'd like to ask another question. Along with this.

Pete, if you know anything about this too.

I'd like to count the number of cells Between these "two identical cells" appearing on the timeline.

Like if this is possible, I'll make another example,

2
44
52
32
12
3 < highlighted
3 < highlighted (if my data appears in column B, is it possible that a script can count the cells between this highlighted cell and the highlighted cells BELOW and actually write the number of cells between highlighted cells in Column C next to the highlighted Cell.)
45
23 < highlighted
23 < highlighted (Column C here to have the number of non-highlighted cells written next to this highlighted cell)
56
54

I know this sounds complicated, but I really need it, I'd appreciate your help and thank you very very much.

[Pete_UK]

I don't quite understand it - can you put it in a sample workbook with the numbers that you expect and why?

Pete

[Quimerreya]

I'm sorry I don't know what workbook is, but I'll try hard to explain it the way I am able to

So here's my cell B

1
2
3
4
5 <highlight
5 <highlight
6
7
8 <highlight
8 <highlight (here's cell C saying: 2) (cause the amount of cells between two 5's and two 8's is 2)
9
10
11
12 <highlight
12 <highlight (here's cell C saying: 3) (cause the amount of cells between two 8's and two 12's is 3)
13
14
15


Now verbally. I have chosen a green color for my highlights, others are left default white right?
so, what I want now, I want the 'script' to count the amount of white cells between green(highlighted) cells and give me this number (of amount of white cells) in the block C next to the green (highlighted) cell.

If this is possible.

I hope you'll understand what I was trying to say.

Thank you so much.

[Pete_UK]

A workbook (i.e. Excel file) is attached. I've put this formula in B2:

=IF(OR(A2=A1,A2=A3),ROWS($1:2),"")

and copied this down. Then this one in C3:

=IF(B3="","",IF(AND(B3<>"",B2=""),"",IF(AND(B3<>"",B4=""),IF(COUNTIF(B$2:B3,">0")>2,B3-MAX(B$1:B1)-2,""),"")))

is also copied down,to give you what I think you want.

Hope this helps.

Pete

[Quimerreya]

Pete.

I've taken exactly the same steps.

I made a totally new excel file, copied my data just to make it match column A and B and C for your formulas.

The first formula about highlighting works perfectly.

Now I took these formulas. Inserted first one in B2 and second one in C3, but nothing happened.

Am I missing something? how do I make this formulas start working?

(I'm sorry for being so annoying and bothering you, thanks for your time again)

[Pete_UK]

Select B2 (with the formula in) and double-click the fill handle (the small black square in the bottom right corner of the cursor) - this will copy the formula down to the bottom of your data.

Do the same with C3.

Hope this helps.

Pete

P.S. It's 2:15am here, so time for bed for me.

[Quimerreya]

Pete,

Thank you so so much, this was very helpful, you're hilarious! Thanks a lot!