bartmaster
New Member
 Joined
 Jan 30, 2019
 Messages
 21
 Office Version

 365
 2016
 Platform

 Windows
Please see below example table.
Column A has random numbers between 1 to 100
Column B has the following formula copied down starting from B2:
I would like to get value of 1 in column B if the row in column A is distinct, else 0.
This works well only for small data sets but I need this to work efficiently for data sets exceeding 100k rows.
I have tried the below VBA code for 50k rows that would basically do the same thing as the formula but also replace formula with the cell value to speed up calculations (I think this is correct).
I have also printed how much time it took to get to a particular row number.
Processing Time:
cell E1  row 1000: 1 sec
cell F1  row 5'000: 6 sec
cell G1  row 10'000: 69 sec
cell H1  row 20'000: 148 sec
cell I1  row 30'000: 237 sec
cell J1  row 40'000: 338 sec
cell K1  row 50'000: 450 sec
Is there any quicker way to achieve checking if a value in a range is a distinct value (that would work for both: numeric and string values)?
I looked through the forum and only found how to extract unique values from a range.
I do not want to copy my data anywhere or delete duplicated rows.
Data set that is being checked (column A) must remain unchanged.
Attached is the screenshot of the example table.
Thanks
Column A has random numbers between 1 to 100
Column B has the following formula copied down starting from B2:
Excel Formula:
=IF(COUNTIF($A$2:A2,A2)=1,1,0)
I would like to get value of 1 in column B if the row in column A is distinct, else 0.
This works well only for small data sets but I need this to work efficiently for data sets exceeding 100k rows.
I have tried the below VBA code for 50k rows that would basically do the same thing as the formula but also replace formula with the cell value to speed up calculations (I think this is correct).
I have also printed how much time it took to get to a particular row number.
VBA Code:
Sub get_unique()
Dim startTime As Double
startTime = Now
Dim cell As Range, xRng As Range, lr As Long
lr = Sheet1.Cells(Sheet1.Rows.Count, 4).End(xlUp).Row
Set xRng = Sheet1.Range("b2:b50000")
For Each cell In xRng.Cells
cell.Formula = "=IF(COUNTIF($A$2:A" & cell.Row & ",A" & cell.Row & ")=1,1,0)"
cell.Value = cell.Value
Select Case cell.Row
Case Is = 100
Range("e2").Value = Round(((Now  startTime) * 60 * 60 * 24), 0)
DoEvents
Case Is = 1000
Range("f2").Value = Round(((Now  startTime) * 60 * 60 * 24), 0)
DoEvents
Case Is = 10000
Range("g2").Value = Round(((Now  startTime) * 60 * 60 * 24), 0)
DoEvents
Case Is = 20000
Range("h2").Value = Round(((Now  startTime) * 60 * 60 * 24), 0)
DoEvents
Case Is = 30000
Range("i2").Value = Round(((Now  startTime) * 60 * 60 * 24), 0)
DoEvents
Case Is = 40000
Range("j2").Value = Round(((Now  startTime) * 60 * 60 * 24), 0)
DoEvents
Case Is = 50000
Range("k2").Value = Round(((Now  startTime) * 60 * 60 * 24), 0)
DoEvents
End Select
Next cell
MsgBox "refresh time: " & Round(((Now  startTime) * 60 * 60 * 24), 0) & " seconds"
End Sub
Processing Time:
cell E1  row 1000: 1 sec
cell F1  row 5'000: 6 sec
cell G1  row 10'000: 69 sec
cell H1  row 20'000: 148 sec
cell I1  row 30'000: 237 sec
cell J1  row 40'000: 338 sec
cell K1  row 50'000: 450 sec
Is there any quicker way to achieve checking if a value in a range is a distinct value (that would work for both: numeric and string values)?
I looked through the forum and only found how to extract unique values from a range.
I do not want to copy my data anywhere or delete duplicated rows.
Data set that is being checked (column A) must remain unchanged.
Attached is the screenshot of the example table.
Thanks