Check if value is duplicate??

[BearGilchrist]

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?

 

[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]

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]

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)

 

[BearGilchrist]

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]

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

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

 

[BearGilchrist]

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]

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?