Finding Values in an array with duplicates values

Question

I have an array of customer names. This array is full of duplicates, and needs to be since other data in the order row may vary. There are no unique identifiers for this data and I need to compare the data from one order against all the rows who have that customer in the array.

I'm having trouble getting the for loop to search all the rows that have a customer match.

Any help would be appreciated!

Dim prodlog As String
Dim orddate As Variant
Dim cus As String
Dim owner As String
Dim orddate2 As Variant
Dim owner2 As String
Dim LogCusts As Variant
LogCusts = Application.Transpose(Range("F3", Range("F" & Rows.count).End(xlUp)))
Dim loglen As Integer
loglen = UBound(LogCusts) - LBound(LogCusts)
Dim cust2 As Variant
For Each cust2 In LogCusts
    Dim custrow As Integer
    custrow = Application.Match(cust2, LogCusts, False) + 1
    prodlog = Range(Cells(custrow, 5), Cells(custrow, 5)).Value
    orddate = Range(Cells(custrow, 2), Cells(custrow, 2)).Value
    cus = Range(Cells(custrow, 6), Cells(custrow, 6)).Value
    owner = Range(Cells(custrow, 7), Cells(custrow, 7)).Value

    databook.Activate
    logjam.Select
    orddate2 = Range(Cells(custrow + 1, 5), Cells(custrow + 1, 5)).Value
    owner2 = Range(Cells(custrow + 1, 7), Cells(custrow + 1, 7)).Value

    If IsEmpty(orddate) Then
        Exit For
    End If

    If IsEmpty(prodlog) Then
        trackbook.Activate
        masterlog.Select
        Range(Cells(custrow, 2), Cells(custrow, 17)).Clear
    Else: While cus = cust2
        If orddate = orddate2 And owner = owner2 Then
            Range(Cells(custrow, 8), Cells(custrow, 8)).Value = prodlog
        End If
    Wend
End If
Next cust2

Here is a method using a Scripting.Dictionary: http://stackoverflow.com/a/11870350/2258 — Richard Morgan, Sep 03 '13 at 20:09

score 1 · Answer 1 · answered Sep 03 '13 at 20:29

There are a couple of ways to do what you want to do. The easiest is probably to use a dictionary like @Richard Morgan suggests in his comment to you.

You can also loop through the array and create a new array with just the correct names.

Sample Code:

Private Sub UniqueArray(oldData as Variant) as Collection
  Set UniqueArray = New Collection

  Dim i as integer
  Dim j as integer
  Dim foundFlag as boolean

  For i = 1 to oldData.Count

    foundFlag = False
    FOr j = i + 1 to oldData.Count

      If oldData(i) = oldData(j) then
        foundFlag = True
      End If
    Next j

    If not foundFlag then UniqueArray.Add oldData(i)
  Next

Again, I think using a dictionary is probably better, but this should work.

score 1 · Answer 2 · edited Nov 27 '13 at 08:54

Wow that looks so complicated, have you tried creating a table next to your current array and use the formula:

=IF(MAX(COUNTIF(A2:A11,A2:A11))>1,"Duplicates","No Duplicates")

that will display Duplicates if there is a duplicate and display No Duplicates if there is non. from A2-A11 of course.

Or to keep things really simple you can use conditional formatting, type in something like

=COUNTIF($B$2:$B$11,B2)=1

for items for values that only appear once. you can slightly modify to get a color scheme for your array. Then throw in a Key on the side that tells which color means how many duplicates.

Don't know if that helps, but I try to keep away from VBA when ever I can.

Finding Values in an array with duplicates values

2 Answers2