Identify repeated numbers in same sequence in a sheet using conditional formatting

Gucci90 · Sep 3, 2018

Hi Excel guru's

I am trying to identify (highlight) repeated numbers in a excel sheet using conditional formatting formula and try use few formulas countif , match and index but it doesn't seem to be working..

any thoughts whether this can be achieved using excel conditional format..

[TABLE="width: 96"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody>[TR]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]44[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]11[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]39[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]39[/TD]
[TD="bgcolor: transparent, align: right"]52[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]44[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]44[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]66[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]66[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]22[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]12[/TD]
[TD="bgcolor: yellow, align: right"]12[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]24[/TD]
[TD="bgcolor: yellow, align: right"]24[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]44[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"]16[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]66[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]22[/TD]
[TD="bgcolor: yellow, align: right"]44[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]44[/TD]
[TD="bgcolor: yellow, align: right"]66[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]66[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"]16[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for any tips.

Peter_SSs · Sep 3, 2018

Welcome to the MrExcel board!

Could you explain your requirement more clearly?
For example, why aren't the others I have highlighted below in green and blue also included?

Book1

A

B

1

2

44

11

3

66

56

4

22

39

5

4

6

39

52

7

2

8

44

9

66

10

22

11

12

24

13

1

14

44

16

15

66

0

16

22

44

17

44

66

18

66

1

19

16

20

0

CF

Gucci90 · Sep 4, 2018

Hi Peter,

Thanks for quick response. For some reasons the colour I manually added didn't show in thread.. I am looking for same info by comparing in to 10000 cells for a repeated numbers in the same sequence.

I would like to highlight/identify the series of numbers in the same sequence ( 2/3/4/5/6 number sequence as per below table), thinking of using conditional format with a formula
i have looked in on YouTube and on this forum there are few formulas suggested but when i try to use below formula and it returns a value (count of 4) but I am not sure whether that is correct or not! i could not find reference or easy way to reconcile that!

=SUM(INT(FREQUENCY(IF(A2:C21=1,COLUMN($A2:$C21)),IF(1-($A$2:$C21=1),COLUMN($A2:$C21)))/1))

but when i try to use same in the conditional format as formula it did nothing..

[TABLE="width: 637"]
<colgroup><col width="56" style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="29" style="width: 22pt; mso-width-source: userset; mso-width-alt: 1060;"> <col width="34" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1243;"> <col width="64" style="width: 48pt;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <col width="36" style="width: 27pt; mso-width-source: userset; mso-width-alt: 1316;"> <col width="64" style="width: 48pt;"> <col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="30" style="width: 23pt; mso-width-source: userset; mso-width-alt: 1097;"> <col width="43" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1572;"> <col width="64" style="width: 48pt;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="36" style="width: 27pt; mso-width-source: userset; mso-width-alt: 1316;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 142, bgcolor: transparent, colspan: 2"]2 number seq repeats[/TD]
[TD="width: 29, bgcolor: transparent"][/TD]
[TD="width: 180, bgcolor: transparent, colspan: 3"]3 number sequence repeats[/TD]
[TD="width: 27, bgcolor: transparent"][/TD]
[TD="width: 170, bgcolor: transparent, colspan: 3"]4 number sequence repeats[/TD]
[TD="width: 30, bgcolor: transparent"][/TD]
[TD="width: 168, bgcolor: transparent, colspan: 3"]5 number sequence repeats[/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]44[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]44[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]39[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]39[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]39[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]39[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]39[/TD]
[TD="bgcolor: transparent, align: right"]52[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]39[/TD]
[TD="bgcolor: transparent, align: right"]52[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]39[/TD]
[TD="bgcolor: transparent, align: right"]52[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]39[/TD]
[TD="bgcolor: transparent, align: right"]52[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]44[/TD]
[TD="bgcolor: yellow, align: right"]44[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]44[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]44[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]44[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]44[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]66[/TD]
[TD="bgcolor: yellow, align: right"]66[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]66[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]66[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]66[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]66[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]22[/TD]
[TD="bgcolor: yellow, align: right"]22[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]22[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]22[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]12[/TD]
[TD="bgcolor: yellow, align: right"]12[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]12[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]44[/TD]
[TD="bgcolor: yellow, align: right"]16[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]44[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]16[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]66[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]66[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]22[/TD]
[TD="bgcolor: yellow, align: right"]44[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]44[/TD]
[TD="bgcolor: yellow, align: right"]66[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]16[/TD]
[TD="bgcolor: yellow, align: right"]16[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]18[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]16[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]18[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]18[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]0[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

For ex 2 number sequence repeating at least 6 times ( 44&66 ) and the same way 22 & 12 repeated twice and 16 & 0 twice as well.. I am trying to highlight that using a formula to count the repeat no and highlight the same using condition format using same formula..

same data for 3 number sequence has 6 occurrences ( 4 occurrences of 44, 66, 22 & 2 occurrences of 1,16,0) and need to the of occurrences on the right ,,

I am planning copy the data set into 5 different sheets and apply the count formula & use the same to highlight the number repeated in the same sequence..

any inputs would be helpful..

Peter_SSs · Sep 4, 2018

I'm afraid that I still do not understand, even for the 2 number sequence repeats. For example,

- Why isn't the first 22 in col A yellow since 66, 22 is repeated?
- Why aren't the '2's yellow since 2, 44 appears in both columns?
- Why isn't the combination of 24, 1 yellow since it appears in both columns?

Instead of quoting formulas that don't appear to work, you need to accurately show & explain the results you want.

If it ends up that this is not feasible with Conditional Formatting, would a macro solution be acceptable?

Gucci90 · Sep 5, 2018

Hi Peter,

Its just me didn't locate that repeat numbers, actually as you pointed i would need that 22 , 2, 24 & 1 to be identified as repeated sequences.

i am after below logic.

1. count the number of occurrences either ascending or descending which ever easier. for example, 44,66 seq repeated 6 times, same way 66,22 repeated 4 times and display those no's in sequence..

I hope this is clear and that's okay if it is macro's or formula's..

[TABLE="width: 485"]
<colgroup><col width="56" style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="11" style="width: 8pt; mso-width-source: userset; mso-width-alt: 402;"> <col width="34" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1243;"> <col width="64" style="width: 48pt;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"> <col width="66" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;"> <col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;"> <tbody>[TR]
[TD="width: 142, bgcolor: transparent, colspan: 2"]2 number seq repeats[/TD]
[TD="width: 83, bgcolor: transparent"] [/TD]
[TD="width: 67, bgcolor: transparent"] [/TD]
[TD="width: 11, bgcolor: #A5A5A5"] [/TD]
[TD="width: 194, bgcolor: transparent, colspan: 3"]3 number sequence repeats[/TD]
[TD="width: 66, bgcolor: transparent"] [/TD]
[TD="width: 83, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="width: 83, bgcolor: transparent"]Count of occurences[/TD]
[TD="width: 67, bgcolor: transparent"]Numbers in seq[/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="width: 66, bgcolor: transparent"]Count of occurences[/TD]
[TD="width: 83, bgcolor: transparent"]Numbers in seq[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]44[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"] 6
[/TD]
[TD="bgcolor: transparent"]44&66[/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]44[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"] 4
[/TD]
[TD="bgcolor: transparent"]44&66&22[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: transparent"] 5
[/TD]
[TD="bgcolor: transparent"]66&22[/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: transparent"] 2
[/TD]
[TD="bgcolor: transparent"]66&22&12[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]39[/TD]
[TD="bgcolor: transparent"] 2
[/TD]
[TD="bgcolor: transparent"]2&44[/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]39[/TD]
[TD="bgcolor: transparent"] 2
[/TD]
[TD="bgcolor: transparent"]1&16&0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"] 2
[/TD]
[TD="bgcolor: transparent"]22&12[/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"] 2
[/TD]
[TD="bgcolor: transparent"]2&44&66[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]39[/TD]
[TD="bgcolor: transparent, align: right"]52[/TD]
[TD="bgcolor: transparent"] 2
[/TD]
[TD="bgcolor: transparent"]12&24[/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]39[/TD]
[TD="bgcolor: transparent, align: right"]52[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]2[/TD]
[TD="bgcolor: yellow, align: right"]2[/TD]
[TD="bgcolor: transparent"] 2
[/TD]
[TD="bgcolor: transparent"]24&1[/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]44[/TD]
[TD="bgcolor: yellow, align: right"]44[/TD]
[TD="bgcolor: transparent"] 2
[/TD]
[TD="bgcolor: transparent"]16&0[/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]44[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]44[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]66[/TD]
[TD="bgcolor: yellow, align: right"]66[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]66[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]66[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]22[/TD]
[TD="bgcolor: yellow, align: right"]22[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]22[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]12[/TD]
[TD="bgcolor: yellow, align: right"]12[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]12[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]24[/TD]
[TD="bgcolor: yellow, align: right"]24[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]1[/TD]
[TD="bgcolor: yellow, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]44[/TD]
[TD="bgcolor: yellow, align: right"]16[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]44[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]16[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]66[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]66[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]22[/TD]
[TD="bgcolor: yellow, align: right"]44[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]44[/TD]
[TD="bgcolor: yellow, align: right"]66[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent, align: right"]44[/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]16[/TD]
[TD="bgcolor: yellow, align: right"]16[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]18[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]16[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: yellow, align: right"]0[/TD]
[TD="bgcolor: yellow, align: right"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: #A5A5A5"] [/TD]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your time

Peter_SSs · Sep 7, 2018

I'm still not understanding/agreeing with some of your expected results (eg why not 1 & 16 in the first set of data above?)

Anyway, I think you will need a macro for this and here is a suggested one to test with. I would suggest testing well with small sample data like we have here before trying on your large set of data.
Given that your real data is large, this code my fail on the red highlighted line if too many different groups of repeating numbers are found. If that happens, post back and we'll look at an alternative for that line of code.

In my test below, the sheet started with just the data shown in columns A:B and the code has produced the other columns.
I have set the code to check for repeats of 2, 3 or 4 numbers. You can alter that by editing the 'Const' line near the start of the code.

Code:

Sub FindPatterns()
  Dim aPatternsToLookFor As Variant, itm As Variant, a As Variant, bits As Variant, Ky As Variant
  Dim d As Object
  Dim r As Long, c As Long, Num As Long, rws As Long, nc As Long, i As Long
  Dim s As String
  
  Const sPatternsToLookFor As String = "2 3 4"  '<- edit to suit what repeats you want to check for
  
  aPatternsToLookFor = Split(sPatternsToLookFor)
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  rws = UBound(a)
  nc = 4
  For Each itm In aPatternsToLookFor
    Num = CLng(itm)
    With Cells(2, nc).Resize(rws, 2)
      .Value = a
      For c = 1 To UBound(a, 2)
        For r = 1 To rws - Num + 1
          s = a(r, c)
          For i = 1 To Num - 1
            s = s & "&" & a(r + i, c)
          Next i
          If d.exists(s) Then
            bits = Split(d(s))
            If UBound(bits) > 0 Then
              .Cells(CLng(bits(1)), CLng(bits(2))).Resize(Num).Interior.Color = vbYellow
              d(s) = bits(0)
            End If
            d(s) = d(s) + 1
            .Cells(r, c).Resize(Num).Interior.Color = vbYellow
          Else
            d(s) = 1 & " " & r & " " & c
          End If
        Next r
      Next c
      For Each Ky In d.Keys()
        If InStr(1, d(Ky), " ") > 0 Then d.Remove Ky
      Next Ky
      .Cells(0, 3).Value = Num & " repeats"
      .Cells(1, 3).Resize(d.Count, 2).Value = Application.Transpose(Array(d.Items, d.Keys))
      .CurrentRegion.Columns.AutoFit
    End With
    nc = nc + 5
    d.RemoveAll
  Next itm
End Sub

My sample data and results:

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

2 repeats

3 repeats

4 repeats

2

44

11

44

11

6

44&66

44

11

4

44&66&22

44

11

2

2&44&66&22

3

66

56

66

56

4

66&22

66

56

2

2&44&66

66

56

2

44&66&22&12

4

22

39

22

39

2

2&44

22

39

2

66&22&12

22

39

2

66&22&12&24

5

4

2

22&12

4

2

22&12&24

4

2

22&12&24&1

6

39

52

39

52

2

12&24

39

52

2

12&24&1

39

52

7

2

24&1

2

1&16&0

2

8

44

3

16&0

44

9

66

2

1&16

66

10

22

11

12

24

13

1

14

44

16

44

16

44

16

44

16

15

66

0

66

0

66

0

66

0

16

22

44

22

44

22

44

22

44

17

44

66

44

66

44

66

44

66

18

66

1

66

1

66

1

66

1

19

16

20

0

Identify Patterns

Gucci90 · Sep 8, 2018

Hi Peter,

that' what I am after and now getting expected results but there is run time error when I tried with diff values.. other than that I think its only looks for 2 rows (A&B) populates the results in rows D to F.. Actually I have data in 40 to 50 rows with varying columns 50 o150 (numbers) for each row.

Thanks..

Peter_SSs · Sep 9, 2018

Gucci90 said:
... there is run time error when I tried with diff values..

For me to even consider possible causes/solutions you would need to give the full error message, what line of code gave the error (use Debug) and details of what "different values" you mean.

Gucci90 said:
... other than that I think its only looks for 2 rows (A&B) populates the results in rows D to F.. Actually I have data in 40 to 50 rows with varying columns 50 o150 (numbers) for each row.

As with a number of other posts in this thread, you have not been careful enough, making it very hard to understand what you actually have and want. This time you are confusing rows and columns. Rows are labelled by numbers, columns are labelled by letters. Perhaps you could try again, very carefully, with that explanation. Remember, we are not looking at your workbook so all we have to go on is what you tell us and what you show us. It needs to be accurate.

If your data is laid out differently to what you have shown/told us so far, then it isn't surprising that the code doesn't do what you want. Again, we cannot deal with what we don't know about.

Identify repeated numbers in same sequence in a sheet using conditional formatting

Gucci90

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Gucci90

New Member

Peter_SSs

MrExcel MVP, Moderator

Gucci90

New Member

Peter_SSs

MrExcel MVP, Moderator

Gucci90

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Identify repeated numbers in same sequence in a sheet using conditional formatting

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock