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

ibmy

Board Regular
Joined
Mar 4, 2020
Messages
134
Office Version
  1. 2016
Platform
  1. Windows
5.31.xlsb
BCDEF
1MAX 3 numbernote:MAX 2 numbernote:
2122,1271st number : 122,1271st number : 122,127
3122,12711
4122,12711
5122,12012nd number: 122,12012nd number: 122,120
6122,12213rd number : 122,122RESET. 1st number : 122,122
7122,127112nd number: 122,127
8122,12211
9122,12211
10122,124RESET. 1st number : 122,124RESET. 1st number : 122,124
11122,12212nd number: 122,12212nd number: 122,122
12122,12211
13122,12211
14122,12913rd number : 122,129RESET. 1st number : 122,129
15122,122112nd number: 122,122
16122,127RESET. 1st number : 122,127RESET. 1st number : 122,127
17122,12312nd number: 122,12312nd number: 122,123
18122,13013rd number : 122,130RESET. 1st number : 122,130
19122,140RESET. 1st number : 122,14012nd number: 122,140
Sheet3

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

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
 
Upvote 0
@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.

Cell Formulas
RangeFormula
F2F2=IF(A2="","",1)
G2G2=IF(A2="","",1)
H2H2=IF(A2="","",IF(F2=1,A2,""))
I2I2=IF(A2="","",IF(F2=2,A2,""))
J2J2=IF(A2="","",IF(F2=3,A2,""))
K2K2=IF(A2="","",1)
L2L2=IF(A2="","",1)
M2M2=IF(A2="","",IF(K2=1,H2,""))
N2N2=IF(A2="","",IF(K2=2,H2,""))
F3:F19F3=IF(A3="","",IF(COUNTIF(H2:J2,A3)=1,"",IF(G2=3,1,G2+1)))
G3:G19G3=IF(A3="","",IF(F3="",G2,IF(G2=3,1,G2+1)))
H3:H19H3=IF(A3="","",IF(F3=1,A3,H2))
I3:I19I3=IF(A3="","",IF(F3=2,A3,IF(F3=1,"",I2)))
J3:J19J3=IF(A3="","",IF(F3=3,A3,IF(F3=1,"",J2)))
K3:K19K3=IF(A3="","",IF(COUNTIF(M2:N2,A3)=1,"",IF(L2=2,1,L2+1)))
L3:L19L3=IF(A3="","",IF(K3="",L2,IF(L2=2,1,L2+1)))
M3:M19M3=IF(A3="","",IF(K3=1,A3,M2))
N3:N19N3=IF(A3="","",IF(K3=2,A3,IF(K3=1,"",N2)))
B2:B19B2=IF(A2="","",IF(F2=1,"",1))
D2:D19D2=IF(A2="","",IF(K2=1,"",1))

Thanks @fjns
 
Last edited:
Upvote 0
Hi, I am very glad that the formula works.
If I have enough knowledge, I am happy to help at any time.
 
Upvote 0
Is this possible to .... code in Excel?
This should be a lot faster.

VBA Code:
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
ABCD
1
2122.127
3122.12711
4122.12711
5122.12011
6122.1221
7122.12711
8122.12211
9122.12211
10122.124
11122.12211
12122.12211
13122.12211
14122.1291
15122.12211
16122.127
17122.12311
18122.1301
19122.1401
Sheet1
 
Upvote 0
Solution
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
 
Upvote 0
1676369896750.png
Glad it seems to have worked for you. :)
 
Upvote 0

Forum statistics

Threads
1,224,815
Messages
6,181,135
Members
453,021
Latest member
Justyna P

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