#### 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