Check if value is duplicate??

BearGilchrist

New Member
Joined
Apr 23, 2018
Messages
10
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 23, 2018
Messages
10
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
Joined
May 28, 2005
Messages
49,420
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 23, 2018
Messages
10

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
49,420
Office Version
  1. 365
Platform
  1. Windows
(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
Joined
Apr 23, 2018
Messages
10
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
Joined
May 28, 2005
Messages
49,420
Office Version
  1. 365
Platform
  1. Windows
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. :confused:

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

Forum statistics

Threads
1,136,323
Messages
5,675,087
Members
419,549
Latest member
EliteBeat

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
Top