# Check if value is duplicate??

#### BearGilchrist

##### New Member
Hi,

I have a table of chronological data and I want to check if a value is unique up until that point in the table. currently using =COUNTIF(\$Q\$1:Q1,Q2) but this is killing excels performace and locking it up for 20 mins. has anyone got better alternatives?

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Joe4

Welcome to the Board!

That seems like an awful long time to run.
Just how much data are we talking about here?
Do you have any other formulas or VBA running?

#### BearGilchrist

##### New Member
I should probably also add that I need it to return a numeric value to incorporate formulae later on, I could be working with anywhere up to 80K rows of data

#### Peter_SSs

##### MrExcel MVP, Moderator
I should probably also add that I need it to return a numeric value
If you need to actually count the values then you would likely need to continue to use COUNTIFS (or a macro).

However, if you just want to check for duplicates and mark those with (any) numeric value, then here are a few other options to try for row 2, copied down.

a) =IF(MATCH(Q2,Q\$1:Q2,0)=ROW(Q2)-ROW(Q\$1)+1,"",1)

b) If you can be certain that no new rows will be added at the top of the sheet, then that can be simplified a little to =IF(MATCH(Q2,Q\$1:Q2,0)=ROW(),"",1)

c) This returns 1 for duplicates and #N/A for non-duplicates =MATCH(Q2,Q\$1:Q1,0)

Last edited:

#### BearGilchrist

##### New Member
It is long, and it gets longer as my data table grows throughout the month. by the end of a month it could be up tp 80K rows of data, but I cant decided on what works best VBS wise, till now it seems to be my only option? Unless im missing something.

#### Peter_SSs

##### MrExcel MVP, Moderator
(or a macro)
You could try this in a copy of your file.
It writes the results in column R. Edit that part right near the end of the code if you want results in a different column.

Rich (BB code):
``````Sub Check_Dupes()
Dim d As Object
Dim a As Variant, b As Variant
Dim i As Long

Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = 1
a = Range("Q1", Range("Q" & Rows.Count).End(xlUp)).Value
ReDim b(1 To UBound(a), 1 To 1)
For i = 1 To UBound(a)
If Not d.exists(a(i, 1)) Then d(a(i, 1)) = 0
b(i, 1) = d(a(i, 1))
d(a(i, 1)) = d(a(i, 1)) + 1
Next i
Range("R1").Resize(UBound(b)).Value = b
End Sub``````

Last edited:

#### BearGilchrist

##### New Member
This seems to be working perfectly, thank you. The line of formula you suggested worked, but when I tried to simplfy it to what you suggested in line B it choked my excel.. odd

#### Peter_SSs

##### MrExcel MVP, Moderator
This seems to be working perfectly, thank you.
That sounds great.

The line of formula you suggested worked, but when I tried to simplfy it to what you suggested in line B it choked my excel.. odd
That seems to contradict the previous statement.

So, have you got something that works satisfactorily or not? If so, to satisfy my curiosity, which method/formula worked best for you?

Replies
2
Views
144
Replies
9
Views
333
Replies
1
Views
382
Replies
5
Views
117
Replies
15
Views
2K

1,171,829
Messages
5,877,795
Members
433,287
Latest member
amna_shahbaz

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

### Which adblocker are you using?

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

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