Results 1 to 7 of 7

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

  1. #1
    New Member
    Join Date
    Sep 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

    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!

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

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

    Try something like this...

    Code:
        'Example driver and month
        Driver = "John": Mo = "March"
        'Read data from sheet
        Data = Range("A2", Range("C" & Rows.Count).End(xlUp)).Value
        
        For i = LBound(Data, 1) To UBound(Data, 1)
            If Data(i, 1) = Driver And Data(i, 2) = Mo Then
                If Truck1 = "" Then
                    Truck1 = Data(i, 3)
                ElseIf Truck2 = "" And Truck1 <> Data(i, 3) Then
                    Truck2 = Data(i, 3)
                ElseIf Truck3 = "" And Truck1 <> Data(i, 3) And Truck2 <> Data(i, 3) Then
                    Truck3 = Data(i, 3)
                End If
            End If
        Next i
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,649
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

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

    Maybe this where arr2 will hold the trucks

    Code:
    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

  4. #4
    New Member
    Join Date
    Sep 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  5. #5
    New Member
    Join Date
    Sep 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  6. #6
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

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

    Code:
        'Example driver and month
        Driver = "John": Mo = "March"
        'Read data from sheet
        Data = Range("C2", Range("H" & Rows.Count).End(xlUp)).Value
        
        For i = LBound(Data, 1) To UBound(Data, 1)
            If Data(i, 1) = Driver And Data(i, 3) = Mo Then
                If Truck1 = "" Then
                    Truck1 = Data(i, 6)
                ElseIf Truck2 = "" And Truck1 <> Data(i, 6) Then
                    Truck2 = Data(i, 6)
                ElseIf Truck3 = "" And Truck1 <> Data(i, 6) And Truck2 <> Data(i, 6) Then
                    Truck3 = Data(i, 6)
                End If
            End If
        Next i
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  7. #7
    New Member
    Join Date
    Sep 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    AlphaFrog,

    Thank you for the answer. That helped me immensely!

    Regards
    Wessel Steyl

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •