Formula - Evaluate for Double Count IF condition is met

[SteveOranjin]

Hello,

I am in the middle of writing a formula that evaluates whether or not a column has any duplicates in it. The problem is, is that I don't know how to make the formula evaluate duplicates where that condition is met.

If the header is "SKU" then I would like the formula to count the number of duplicates and return the number. So,

IF column C has header "SKU"
Then
Count numebr of duplicates.

If Column does NOT have "SKU" in title, than do not count duplicates.

Steve

 

[SteveOranjin]

They are Alphanumeric

 

[SteveOranjin]

I feel like this should work.

=(SUM(SIGN(FREQUENCY(IFERROR(IF(OFFSET('Data Sheet'!A2:A500000,0,MATCH("SKU",'Data Sheet'!A1:BB1,0)-1)<>"",MATCH(OFFSET('Data Sheet'!A2:A500000,0,MATCH("SKU",'Data Sheet'!A1:BB1,0)-1),OFFSET('Data Sheet'!A2:A500000,0,MATCH("SKU",'Data Sheet'!A1:BB1,0)-1),0),""),1),ROW('Data Sheet'!A2:A500000)-ROW(A2)+1))))

 

[Eric W]

I tried it on my sheet, and your formula does work with a row limit of 500,000, but there is a noticeable lag. Probably tolerable if you don't have any other big formulas.

If you want to try the formula that looks for the last row, try this:

Go to the Formulas tab > Name Manager > New: > and create a new Name called SKUColumn. In the Refers to: box put:

=OFFSET('Data Sheet'!$A$2,0,MATCH("SKU",'Data Sheet'!$A$1:$F$1,0)-1,MATCH("ZZZZZZ",OFFSET('Data Sheet'!$A:$A,0,MATCH("SKU",'Data Sheet'!$A$1:$F$1,0)-1))-1)

and click OK.


Given that, this formula will tell you how many unique SKUs you have in that column:

=SUM(SIGN(FREQUENCY(IF(SKUColumn<>"",MATCH(SKUColumn,SKUColumn,0),""),ROW(SKUColumn)-ROW(INDEX(SKUColumn,1))+1)))

with Control+Shift+Enter

and this one just tells you if there are duplicates:

=IF(MAX(FREQUENCY(IF(SKUColumn<>"",MATCH(SKUColumn,SKUColumn,0),""),ROW(SKUColumn)-ROW(INDEX(SKUColumn,1))+1))=1,"No Duplicates","Duplicates")

with Control+Shift+Enter.

 

[SteveOranjin]

It seems like this operation takes forever for excel to perform.

Also, will I have to enter Cntrl + Shift + Enter everytime I have new data and clear the sheet out?

Hope you are well,

Steve

 

[Eric W]

If you just clear the data and leave the formulas alone, then you won't need to re-enter Control+Shift+Enter.

As far as the performance, all I can say is that a lot of data requires a lot of time. The other option I can think of would be to write a VBA macro that performs the operations on an on-demand basis. Every time you refresh your data, you rerun the macro. A well written macro should be faster than the formulas. Let me know if that's of interest.

 

[SteveOranjin]

If you just clear the data and leave the formulas alone, then you won't need to re-enter Control+Shift+Enter.

As far as the performance, all I can say is that a lot of data requires a lot of time. The other option I can think of would be to write a VBA macro that performs the operations on an on-demand basis. Every time you refresh your data, you rerun the macro. A well written macro should be faster than the formulas. Let me know if that's of interest.

That is of interest.

 

[Eric W]

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu click Insert > Module. Paste this code into the window that opens:

Sub CountSKUs()
Dim Headers As Range, Result1 As Range, Result2 As Range
Dim MyDict As Object, c As Range, ctr As Long, i As Long, MyData As Variant

    Set Headers = Range("A1:F1")
    Set Result1 = Range("H1")
    Set Result2 = Range("K1")
    
    Set MyDict = CreateObject("Scripting.Dictionary")
    On Error GoTo Oops:
    Set c = Headers.Find("SKU").Offset(1)
        
    MyData = Range(c, Cells(Rows.Count, c.Column).End(xlUp)).Value
    ctr = 0
    For i = 1 To UBound(MyData)
        If MyData(i, 1) <> "" Then
            MyDict(CStr(MyData(i, 1))) = 1
            ctr = ctr + 1
        End If
    Next i

    Result1.Value = "# of unique SKUs"
    Result1.Offset(1) = MyDict.Count
    Result2.Value = "Duplicates?"
    Result2.Offset(1).Value = IIf(MyDict.Count = ctr, "No", "Yes")

    Exit Sub
Oops:
    MsgBox "No header SKU column found"
End Sub

Change the ranges in red to match your sheet. Headers is your headers, Result1 will say "# of unique SKUs" and the cell below it will have the count, Result2 will say "Duplicates" and the cell below it "Yes" or "No".

Press Alt-Q to close the editor. From your Excel sheet, press Alt-F8, select CountSKUs and click run. I tested it on 500,000 lines, and it took about 5.5 seconds. Your results may vary. Let me know how it works for you.

 

[SteveOranjin]

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu click Insert > Module. Paste this code into the window that opens:

Sub CountSKUs()
Dim Headers As Range, Result1 As Range, Result2 As Range
Dim MyDict As Object, c As Range, ctr As Long, i As Long, MyData As Variant

    Set Headers = Range("A1:F1")
    Set Result1 = Range("H1")
    Set Result2 = Range("K1")
    
    Set MyDict = CreateObject("Scripting.Dictionary")
    On Error GoTo Oops:
    Set c = Headers.Find("SKU").Offset(1)
        
    MyData = Range(c, Cells(Rows.Count, c.Column).End(xlUp)).Value
    ctr = 0
    For i = 1 To UBound(MyData)
        If MyData(i, 1) <> "" Then
            MyDict(CStr(MyData(i, 1))) = 1
            ctr = ctr + 1
        End If
    Next i

    Result1.Value = "# of unique SKUs"
    Result1.Offset(1) = MyDict.Count
    Result2.Value = "Duplicates?"
    Result2.Offset(1).Value = IIf(MyDict.Count = ctr, "No", "Yes")

    Exit Sub
Oops:
    MsgBox "No header SKU column found"
End Sub

Change the ranges in red to match your sheet. Headers is your headers, Result1 will say "# of unique SKUs" and the cell below it will have the count, Result2 will say "Duplicates" and the cell below it "Yes" or "No".

Press Alt-Q to close the editor. From your Excel sheet, press Alt-F8, select CountSKUs and click run. I tested it on 500,000 lines, and it took about 5.5 seconds. Your results may vary. Let me know how it works for you.

Please tell me if I'm wrong, it looks like that Macro is designed to enter the results into a sheet.

Is there a way for that macro to work so the results JUST enter the msg box? So you'd have to run it every time to see? That might be a little easier.

As far as I can tell, it works great. It didn't like how I changed the reference (in red) to a particular sheet.

Let me know if I'm pushing my luck here.

 

[Eric W]

Try this:

Sub CountSKUs()
Dim Headers As Range, Result1 As Range, Result2 As Range
Dim MyDict As Object, c As Range, ctr As Long, i As Long, MyData As Variant


    Set Headers = Sheets("Sheet4").Range("A1:F1")
    
    Set MyDict = CreateObject("Scripting.Dictionary")
    On Error GoTo Oops:
    Set c = Headers.Find("SKU")
        
    MyData = c.Offset(1).Resize(c.Offset(Rows.Count - c.Row).End(xlUp).Row - c.Row).Value
    ctr = 0
    For i = 1 To UBound(MyData)
        If MyData(i, 1) <> "" Then
            MyDict(CStr(MyData(i, 1))) = 1
            ctr = ctr + 1
        End If
    Next i
    
    MsgBox "# of unique SKUs: " & MyDict.Count & vbCrLf & IIf(MyDict.Count < ctr, "Duplicates", "")
    Exit Sub
Oops:
    MsgBox "No header SKU column found"
End Sub

You can see how to put in a range on another sheet, and the results show in a message box. The SKU count, and the word "Duplicates" if there are any.

 

[SteveOranjin]

Try this:

Sub CountSKUs()
Dim Headers As Range, Result1 As Range, Result2 As Range
Dim MyDict As Object, c As Range, ctr As Long, i As Long, MyData As Variant


    Set Headers = Sheets("Sheet4").Range("A1:F1")
    
    Set MyDict = CreateObject("Scripting.Dictionary")
    On Error GoTo Oops:
    Set c = Headers.Find("SKU")
        
    MyData = c.Offset(1).Resize(c.Offset(Rows.Count - c.Row).End(xlUp).Row - c.Row).Value
    ctr = 0
    For i = 1 To UBound(MyData)
        If MyData(i, 1) <> "" Then
            MyDict(CStr(MyData(i, 1))) = 1
            ctr = ctr + 1
        End If
    Next i
    
    MsgBox "# of unique SKUs: " & MyDict.Count & vbCrLf & IIf(MyDict.Count < ctr, "Duplicates", "")
    Exit Sub
Oops:
    MsgBox "No header SKU column found"
End Sub

You can see how to put in a range on another sheet, and the results show in a message box. The SKU count, and the word "Duplicates" if there are any.

It returns 2 no matter what for duplicates, and doesn't provide a count.