Most number repetitive/duplicate occurring in SEQUENCE

[ibmy]

Hi,

Can someone help me, sorry for bad English since it is not my native language here.

Ok, I want to find what number in column show "most repetitive/duplicate in SEQUENCE". Im dealing about 100 000 data in a single column.

Here what i have tried:

(1)
I tried use the "most frequently occurring number, =MODE(range) ". I found out that it is not necessary for "most frequently occurring number" to be "most in repetitive/duplicate in sequence".

What I mean is, let say, number 7.77 is the "most frequently occurring number" in column A, but it is not appear to be in sequence. The number "most repetitive/duplicate in sequence" can be the "least frequently occurring number" in column.

What I means "sequence" is, in column A, in row 4,5,6,7,8,9 | 101,102,103,104 | 333,334,345,346,etc.. show that 6.66 is the ""most repetitive/duplicate in SEQUENCE".

While the number 7.77 is "most frequently occurring number" but not in "sequence", it show at random row 11,67,102,678,etc...

(2)
I tried use Conditional Formatting to highlight duplicate. The reason I use this is if I can manually (scrolling) detect duplicate which number to be appear repetitive in sequence.

The problem with this method is there are so many duplicate numbers Im dealing with, 100 000 data but the specific I want is , "the most repetitive/duplicate in sequence" not just random row.

...

Any solution is welcomed, either :

i- Highlight only number in repetitive/duplicate in sequence. ( can be tedious since 100 000 data is too much for me to manually checking (Scrolling).

*ii- Formula to show most number in repetitive/duplicate in sequence. ( I prefer this one)

iii- Or any other method.

Thanks for reading my problem & sorry for bad english.

 

[Akuini]

Hi, ibmy. Welcome to MrExcel.

Try this:
Data in col A, result in C1:

Sub a1126361a()
'https://www.mrexcel.com/board/threads/most-number-repetitive-duplicate-occurring-in-sequence.1126361/
Dim i As Long, j As Long
Dim z As Double, x As Double, y As Double
Dim va

va = Range("A1", Cells(Rows.Count, "A").End(xlUp))
For i = 1 To UBound(va, 1)
 j = i
    Do
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While va(i, 1) = va(i - 1, 1)
    i = i - 1
    z = i - j + 1
    If z > x Then x = z: y = va(j, 1)

Next

Range("C1") = y
End Sub

Example:

Book1
	A	B	C
1	6.3		2.3
2	2.2
3	2.2
4	3.2
5	1.3
6	1.3
7	3.3
8	3.3
9	2.3
10	2.3
11	2.3
12	2.3
13	1.3
14	3.3
15	2.3
16	2.3
17	1.3
18	1.3
Sheet2

If you want I can amend the code as UDF (User Defined Function) so you can use it as formula.

 

[DanteAmor]

Hi and welcome to MrExcel!

With the following Conditional Formatting the first cell where the largest sequence starts is highlighted.
Start putting your numbers in cell A2 as shown in the following:

Dante Amor
	A
1	NUMBER
2	67
3	67
4	67
5	68
6	67
7	67
8	67
9	67
10	65
11	65
12	65
13	65
14	65
15	65
16	65
17	67
18	67
19	65
20	67
21	67
Sheet1

Formula for conditional formating

=COUNTIF(OFFSET(A1,1,0,MAX(FREQUENCY(IF(($A$3:$A$210<>"")*($A$3:$A$210-$A$2:$A$209=0),ROW($A$3:$A$210)),IF($A$3:$A$210-$A$2:$A$209<>0,ROW($A$3:$A$210))))+1),A2)=MAX(FREQUENCY(IF(($A$3:$A$210<>"")*($A$3:$A$210-$A$2:$A$209=0),ROW($A$3:$A$210)),IF($A$3:$A$210-$A$2:$A$209<>0,ROW($A$3:$A$210))))+1

 

[lrobbo314]

A Power Query way.

Book1
	A	B	C	D
1	Raw		Raw	Count
2	6.3		2.3	4
3	2.2
4	2.2
5	3.2
6	1.3
7	1.3
8	3.3
9	3.3
10	2.3
11	2.3
12	2.3
13	2.3
14	1.3
15	3.3
16	2.3
17	2.3
18	1.3
19	1.3
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Raw"}, {{"Count", each Table.RowCount(_), type number}},GroupKind.Local),
    Filter = Table.SelectRows(Group, each [Count] = List.Max(Group[Count]))
in
    Filter