Get up to 3 unique values based on two criteria and put in variables in VBA

[dwsteyl]

Hi guys,

I've searched high and low for a solution to my specific problem, but to no avail. Hence this post.

I am developing an Excel database of drivers and the trucks they drove. These entries are being made via a userform throughout the month as they use the truck. The basic table as captured in Excel looks like this:

Name	Month	Truck
James	January	Truck A
Jock	February	Truck B
John	March	Truck C
Keith	January	Truck B
James	February	Truck A
Jock	March	Truck C
John	January	Truck C
Keith	February	Truck B
John	March	Truck B
Jock	January	Truck C
John	February	Truck C
Keith	March	Truck B
James	January	Truck C
Jock	February	Truck A
John	March	Truck B

<tbody>
</tbody>

I then have a userform that will be used for basic reporting (this is where my question originated from). On that userform one can select the driver name in one combobox, and the month in another combobox. After that a button is clicked to load the unique trucks that the given driver drove with during that month.

Any given driver will drive with at most three trucks in any given month, but can use any of those three trucks multiple times.

I therefore need VBA code that will get all the unique trucks a specific driver drove with, and save them in Variable1, Variable2 and Variable3. The data type for this can be string or variant. If a driver only drove with one truck only Variable1 will have data in it. It is fine if the other variables are declared - they will just be NULL value and will not result in anything.

So, for example - if driver John is selected, with month March, the unique trucks driven would be Truck C and Truck B, which can be saved in Variable1 and Variable2 (this will then be transfered to textboxes and used for other functions).

The reason this became complicated for me is that John drove more than once with Truck B (could be true for any other truck or all of them). But since that truck is a unique value I only need to know that the truck was driven by John for that specific month. A driver can therefore use any truck multiple times during any given month.

I hope I got my problem across. Please feel free to ask questions if anything is unclear.

I have tried code for this, but I feel so far from the mark that I am not going to post it.

Thanks in advance!

 

[AlphaFrog]

Try something like this...

    [color=green]'Example driver and month[/color]
    Driver = "John": Mo = "March"
    [color=green]'Read data from sheet[/color]
    Data = Range("A2", Range("C" & Rows.Count).End(xlUp)).Value
    
    [color=darkblue]For[/color] i = [color=darkblue]LBound[/color](Data, 1) [color=darkblue]To[/color] [color=darkblue]UBound[/color](Data, 1)
        [color=darkblue]If[/color] Data(i, 1) = Driver And Data(i, 2) = Mo [color=darkblue]Then[/color]
            [color=darkblue]If[/color] Truck1 = "" [color=darkblue]Then[/color]
                Truck1 = Data(i, 3)
            [color=darkblue]ElseIf[/color] Truck2 = "" And Truck1 <> Data(i, 3) [color=darkblue]Then[/color]
                Truck2 = Data(i, 3)
            [color=darkblue]ElseIf[/color] Truck3 = "" And Truck1 <> Data(i, 3) And Truck2 <> Data(i, 3) [color=darkblue]Then[/color]
                Truck3 = Data(i, 3)
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i

 

[steve the fish]

Maybe this where arr2 will hold the trucks

Sub drivers()

Dim sh As Worksheet, lr As Long, arr, arr2, drv As String, mon As String, i As Long

Set sh = Sheets("Sheet1")
lr = sh.Range("A" & sh.Rows.Count).End(xlUp).Row
arr = sh.Range("A1:C" & lr)

dvr = "James"
mon = "January"

With CreateObject("Scripting.Dictionary")
    For i = LBound(arr) To UBound(arr)
        If arr(i, 1) = dvr Then
            If arr(i, 2) = mon Then
                If Not IsMissing(arr(i, 3)) Then .Item(arr(i, 3)) = 1
            End If
        End If
    Next
    arr2 = .keys
End With

End Sub

 

[dwsteyl]

AlphaFrog,

Thank you for the quick response.

The code you gave works just fine. Just one thing I did not mention in my original post. The drivers, month and trucks are part of a bigger database. Driver is in column C, month in Column E and Trucks in Column H.

Would it therefore be possible to change the code to evaluate the criteria over said columns and then assign to the three variables?

Thank you for sharing your expertise!

 

[dwsteyl]

AlphaFrog,

Thank you for the quick response.

The code you gave works just fine. Just one thing I did not mention in my original post. The drivers, month and trucks are part of a bigger database. Driver is in column C, month in Column E and Trucks in Column H.

Would it therefore be possible to change the code to evaluate the criteria over said columns and then assign to the three variables?

Thank you for sharing your expertise!

 

[AlphaFrog]

    [color=green]'Example driver and month[/color]
    Driver = "John": Mo = "March"
    [color=green]'Read data from sheet[/color]
    Data = Range("C2", Range("H" & Rows.Count).End(xlUp)).Value
    
    [color=darkblue]For[/color] i = [color=darkblue]LBound[/color](Data, 1) [color=darkblue]To[/color] [color=darkblue]UBound[/color](Data, 1)
        [color=darkblue]If[/color] Data(i, 1) = Driver And Data(i, 3) = Mo [color=darkblue]Then[/color]
            [color=darkblue]If[/color] Truck1 = "" [color=darkblue]Then[/color]
                Truck1 = Data(i, 6)
            [color=darkblue]ElseIf[/color] Truck2 = "" And Truck1 <> Data(i, 6) [color=darkblue]Then[/color]
                Truck2 = Data(i, 6)
            [color=darkblue]ElseIf[/color] Truck3 = "" And Truck1 <> Data(i, 6) And Truck2 <> Data(i, 6) [color=darkblue]Then[/color]
                Truck3 = Data(i, 6)
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i

 

[dwsteyl]

AlphaFrog,

Thank you for the answer. That helped me immensely!

Regards
Wessel Steyl