Keep Assign "1" to Max Number 3/2 in a Set of Data

[ibmy]

5.31.xlsb
	B	C	D	E	F
1		MAX 3 number	note:	MAX 2 number	note:
2	122,127		1st number : 122,127		1st number : 122,127
3	122,127	1		1
4	122,127	1		1
5	122,120	1	2nd number: 122,120	1	2nd number: 122,120
6	122,122	1	3rd number : 122,122		RESET. 1st number : 122,122
7	122,127	1		1	2nd number: 122,127
8	122,122	1		1
9	122,122	1		1
10	122,124		RESET. 1st number : 122,124		RESET. 1st number : 122,124
11	122,122	1	2nd number: 122,122	1	2nd number: 122,122
12	122,122	1		1
13	122,122	1		1
14	122,129	1	3rd number : 122,129		RESET. 1st number : 122,129
15	122,122	1		1	2nd number: 122,122
16	122,127		RESET. 1st number : 122,127		RESET. 1st number : 122,127
17	122,123	1	2nd number: 122,123	1	2nd number: 122,123
18	122,130	1	3rd number : 122,130		RESET. 1st number : 122,130
19	122,140		RESET. 1st number : 122,140	1	2nd number: 122,140
Sheet3

Is this possible to formulate or code in Excel?
Sample of 600k row data.

 

[fjns]

Hi, see the linked file (with 9 auxiliary columns) for a possible solution...

The formulas used in the table:
B2: =IF(A2="","",IF(F2=1,"",1)) (Range: B2:B19)
C2: =IF(A2="","","1st number : "&TEXT(A2,"0,000"))
C3: =IF(A3="","",IF(F3="","",IF(F3=1,"RESET. 1st",IF(F3=2,"2nd","3rd"))&" number : "&TEXT(A3,"0,000"))) (Range: C3:C19)
D2: =IF(A2="","",IF(K2=1,"",1)) (Range: D2:D19)
E2: =IF(A2="","","1st number : "&TEXT(A2,"0,000"))
E3: =IF(A3="","",IF(K3="","",IF(K3=1,"RESET. 1st","2nd")&" number : "&TEXT(A3,"0,000"))) (Range: E3:E19)

531.xlsx

 

[ibmy]

@fjns
The formula works.
It took around 30 seconds to complete "Max 3 Number" of 270k row data. Have one solution is better than nothing.
Appreaciate the solution since I waited for a week.

I would like to apologise for not explain clearly what I wanted.
I expected result in Column B & Column D.
Column C & Column E are the idea of how I expected the formula working. I choose wrote the explanation in Sheet instead of in thread.
Sorry for the misunderstood to the reader of my thread.

5.31.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Sample	Max 3		Max 2		AuxC1	AuxC2	AuxC3	AuxC4	AuxC5	AuxC6	AuxC7	AuxC8	AuxC9
2	122,127					1	1	122127			1	1	122127
3	122,127	1		1			1	122127				1	122127
4	122,127	1		1			1	122127				1	122127
5	122,120	1		1		2	2	122127	122120		2	2	122127	122120
6	122,122	1				3	3	122127	122120	122122	1	1	122122
7	122,127	1		1			3	122127	122120	122122	2	2	122122	122127
8	122,122	1		1			3	122127	122120	122122		2	122122	122127
9	122,122	1		1			3	122127	122120	122122		2	122122	122127
10	122,124					1	1	122124			1	1	122124
11	122,122	1		1		2	2	122124	122122		2	2	122124	122122
12	122,122	1		1			2	122124	122122			2	122124	122122
13	122,122	1		1			2	122124	122122			2	122124	122122
14	122,129	1				3	3	122124	122122	122129	1	1	122129
15	122,122	1		1			3	122124	122122	122129	2	2	122129	122122
16	122,127					1	1	122127			1	1	122127
17	122,123	1		1		2	2	122127	122123		2	2	122127	122123
18	122,130	1				3	3	122127	122123	122130	1	1	122130
19	122,140			1		1	1	122140			2	2	122130	122140
test11

Cell Formulas
Range	Formula
F2	F2	=IF(A2="","",1)
G2	G2	=IF(A2="","",1)
H2	H2	=IF(A2="","",IF(F2=1,A2,""))
I2	I2	=IF(A2="","",IF(F2=2,A2,""))
J2	J2	=IF(A2="","",IF(F2=3,A2,""))
K2	K2	=IF(A2="","",1)
L2	L2	=IF(A2="","",1)
M2	M2	=IF(A2="","",IF(K2=1,H2,""))
N2	N2	=IF(A2="","",IF(K2=2,H2,""))
F3:F19	F3	=IF(A3="","",IF(COUNTIF(H2:J2,A3)=1,"",IF(G2=3,1,G2+1)))
G3:G19	G3	=IF(A3="","",IF(F3="",G2,IF(G2=3,1,G2+1)))
H3:H19	H3	=IF(A3="","",IF(F3=1,A3,H2))
I3:I19	I3	=IF(A3="","",IF(F3=2,A3,IF(F3=1,"",I2)))
J3:J19	J3	=IF(A3="","",IF(F3=3,A3,IF(F3=1,"",J2)))
K3:K19	K3	=IF(A3="","",IF(COUNTIF(M2:N2,A3)=1,"",IF(L2=2,1,L2+1)))
L3:L19	L3	=IF(A3="","",IF(K3="",L2,IF(L2=2,1,L2+1)))
M3:M19	M3	=IF(A3="","",IF(K3=1,A3,M2))
N3:N19	N3	=IF(A3="","",IF(K3=2,A3,IF(K3=1,"",N2)))
B2:B19	B2	=IF(A2="","",IF(F2=1,"",1))
D2:D19	D2	=IF(A2="","",IF(K2=1,"",1))

Thanks @fjns

 

[fjns]

Hi, I am very glad that the formula works.
If I have enough knowledge, I am happy to help at any time.

 

[Peter_SSs]

Is this possible to .... code in Excel?

This should be a lot faster.

Sub MaxNums()
  Dim d1 As Object, d2 As Object
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long
  
  Set d1 = CreateObject("Scripting.Dictionary")
  Set d2 = CreateObject("Scripting.Dictionary")
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    ReDim b(1 To UBound(a), 1 To 1)
    ReDim c(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      If d1.exists(a(i, 1)) Then
        b(i, 1) = 1
      Else
        If d1.Count = 3 Then
          d1.RemoveAll
          d1(a(i, 1)) = Empty
        Else
          If d1.Count > 0 Then b(i, 1) = 1
          d1(a(i, 1)) = Empty
        End If
      End If
      If d2.exists(a(i, 1)) Then
        c(i, 1) = 1
      Else
        If d2.Count = 2 Then
          d2.RemoveAll
          d2(a(i, 1)) = Empty
        Else
          If d2.Count > 0 Then c(i, 1) = 1
          d2(a(i, 1)) = Empty
        End If
      End If
    Next i
    .Offset(, 1).Value = b
    .Offset(, 3).Value = c
  End With
End Sub

My sample data in column A, code results in columns B & D

ibmy.xlsm
	A	B	C	D
1
2	122.127
3	122.127	1		1
4	122.127	1		1
5	122.120	1		1
6	122.122	1
7	122.127	1		1
8	122.122	1		1
9	122.122	1		1
10	122.124
11	122.122	1		1
12	122.122	1		1
13	122.122	1		1
14	122.129	1
15	122.122	1		1
16	122.127
17	122.123	1		1
18	122.130	1
19	122.140			1
Sheet1

 

[fjns]

Hi, I created a new table in which the number of the set can be set in cell A1. The formulas work not only for 2 and 3, but for all positive integers.

The formulas used in the new table:

B2: =IF(A2="","",IF(D2=1,"",1)) (Range: B2:B19)
C2: =IF(A2="","","number "&D2&" : "&TEXT(A2,"0,000"))
C3: =IF(A3="","",IF(D3="","",IF(D3=1,"RESET. ","")&" number "&D3&" : "&TEXT(A3,"0,000"))) (Range: C3:C19)

531N2.xlsx
531N3.xlsx
531N4.xlsx
531N5.xlsx

 

[Peter_SSs]

Glad it seems to have worked for you.