Finding Values in an array with duplicates values

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


2 Answers
2

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.

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.

Duplicates

No Duplicates

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.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.