Solved Multi-Select List Box, Distinct Values

[MattBaldry]

Hi Forum Minds,

I have found the below code on here which works almost perfectly.

Display Selection of a List Box in a Text Box

    Dim strSelected As String
    Dim varItem As Variant

    With Me.lstLiveWorksOrderList
        For Each varItem In .ItemsSelected
            strSelected = strSelected & ", " & .Column(2, varItem)
        Next varItem
        Me.txtSelectedCustomerOrders = Mid(strSelected, 2)
    End With

How would I be able to change this to only show unique/distinct values?

~Matt

 

[Mark_]

Easiest way is to make sure your source is distinct. Not sure what you are really trying to do with this as it looks like you are parsing out part of field after selecting. Is that something you could break out before creating your list?

 

[DHookom]

I would use Instr() to determine if the item already exists. There may be an issue with items that end with the same letters.

    Dim strSelected As String
    Dim varItem As Variant

    With Me.lstLiveWorksOrderList
        For Each varItem In .ItemsSelected
            If Instr(strSelected, .Column(2,varItem) & ",") = 0 Then
                strSelected = strSelected & ", " & .Column(2, varItem)
            End If
        Next varItem
        Me.txtSelectedCustomerOrders = Mid(strSelected, 2)
    End With

 

[MattBaldry]

Easiest way is to make sure your source is distinct. Not sure what you are really trying to do with this as it looks like you are parsing out part of field after selecting. Is that something you could break out before creating your list?

Hi Mark,

On this occasion I cannot make this column distinct as the main column has different values.

Please see below for some sample data.

The information I need to pass to our customer is their order number and their part numbers. The Works Order numbers are for our own internal reference.

Hopefully this makes sense.

~Matt

 

[MajP]

   Dim strSelected As String
    Dim varItem As Variant
    Dim aItems() As String
    Dim newItem As String
    Dim i As Integer
    Dim found As Boolean
    With Me.lstLiveWorksOrderList
        For Each varItem In .ItemsSelected
            newItem = .Column(2, varItem)
            aItems = Split(strSelected, ", ")
            found = False
            For i = 0 To UBound(aItems)
               If Trim(aItems(i)) = Trim(newItem) Then found = True
               Exit For
            Next i
            If Not found Then
              If strSelected = "" Then
                strSelected = newItem
              Else
                strSelected = strSelected & ", " & newItem
             End If
            End If
        Next varItem
        Me.txtSelectedCustomerOrders = strSelected
    End With
End Sub

You could probably could simply use instr instead of the split and loop. But that may cause problem if you have substrings ( ex. 18931 and 189)

 

[MattBaldry]

   Dim strSelected As String
    Dim varItem As Variant
    Dim aItems() As String
    Dim newItem As String
    Dim i As Integer
    Dim found As Boolean
    With Me.lstLiveWorksOrderList
        For Each varItem In .ItemsSelected
            newItem = .Column(2, varItem)
            aItems = Split(strSelected, ", ")
            found = False
            For i = 0 To UBound(aItems)
               If Trim(aItems(i)) = Trim(newItem) Then found = True
               Exit For
            Next i
            If Not found Then
              If strSelected = "" Then
                strSelected = newItem
              Else
                strSelected = strSelected & ", " & newItem
             End If
            End If
        Next varItem
        Me.txtSelectedCustomerOrders = strSelected
    End With
End Sub

You could probably could simply use instr instead of the split and loop. But that may cause problem if you have substrings ( ex. 18931 and 189)

Thanks Maj,

I did try the instr, but it always duplicated the first selection. I shall give your code a try when I get to work.

~Matt

 

[MattBaldry]

   Dim strSelected As String
    Dim varItem As Variant
    Dim aItems() As String
    Dim newItem As String
    Dim i As Integer
    Dim found As Boolean
    With Me.lstLiveWorksOrderList
        For Each varItem In .ItemsSelected
            newItem = .Column(2, varItem)
            aItems = Split(strSelected, ", ")
            found = False
            For i = 0 To UBound(aItems)
               If Trim(aItems(i)) = Trim(newItem) Then found = True
               Exit For
            Next i
            If Not found Then
              If strSelected = "" Then
                strSelected = newItem
              Else
                strSelected = strSelected & ", " & newItem
             End If
            End If
        Next varItem
        Me.txtSelectedCustomerOrders = strSelected
    End With
End Sub

You could probably could simply use instr instead of the split and loop. But that may cause problem if you have substrings ( ex. 18931 and 189)

Hi Maj,

It almost works. The first selection only has the unique value, but anything thereafter lists them all.

3 of each selected, displays unique first order, 3 duplicates thereafter.

1 and 3 selected, same result as above.

 

[Gasman]

You are comparing on third column?
All of those are unique?

 

[MattBaldry]

You are comparing on third column?
All of those are unique?

Hi Gasman,

In this example they are, but not always. I have used the second for testing purposes here though as there are 3 different values in the list.

The finished setup will use unique information from the first 3 columns in the report, but if I can get the test data working on any column, it will work on them all.

~Matt

 

[cheekybuddha]

Try this variation

    Dim strSelected As String
    Dim varItem As Variant
    Dim delim As String

    delim = ", "
    With Me.lstLiveWorksOrderList
        For Each varItem In .ItemsSelected
            If InStr(delim & strSelected & delim, (.Column(2,varItem) & delim) = 0 Then
                strSelected = strSelected & delim & .Column(2, varItem)
            End If
        Next varItem
        Me.txtSelectedCustomerOrders = Mid(strSelected, Len(delim))
    End With

 

[MattBaldry]

Try this variation

    Dim strSelected As String
    Dim varItem As Variant
    Dim delim As String

    delim = ", "
    With Me.lstLiveWorksOrderList
        For Each varItem In .ItemsSelected
            If InStr(delim & strSelected & delim, (.Column(2,varItem) & delim) = 0 Then
                strSelected = strSelected & delim & .Column(2, varItem)
            End If
        Next varItem
        Me.txtSelectedCustomerOrders = Mid(strSelected, Len(delim))
    End With

Thanks David,

This worked, but there was a single ) missing after the & delim) = 0

I shall use this now and see if it is to the liking of the people here who make these demands.

Thank you for your help.

~Matt

 

[cheekybuddha]

This worked, but there was a single ) missing after the & delim) = 0

You could equally have removed the extra ( before .Column !

(Apologies, I didn't test )

Glad it works for you

 

[MattBaldry]

Image below for the finished solution.

Thank you all for your help.

 

[cheekybuddha]

Just in case it isn't obvious from the code, the trick is to add the delimiter you use to separate your values in the string before and after the value you seek.

That way you avoid matching on partials.

However, you also have to avoid the gotcha where the string being searched does not have a trailing delimiter, so it must be added for the purposes of the InStr(). Otherwise you will miss a match where the searched value was the last added to the string.

I think this is an old trick I got from @theDBguy many moons ago!

 

[MajP]

@MattBaldry,
Trying to figure out why that did not work and I must have copied something wrong. Sorry about that

I copied this and did not enclose the If

  For i = 0 To UBound(aItems)
     If Trim(aItems(i)) = Trim(newItem) Then found = True
     Exit For
  Next i

Which does not make sense and will kick out after the first item in the loop
The correct was

  For i = 0 To UBound(aItems)
        If Trim(aItems(i)) = Trim(newItem) Then
            found = True
             Exit For
        end if
  Next i

 

[cheekybuddha]

@MattBaldry,
Trying to figure out why that did not work and I must have copied something wrong. Sorry about that

I copied this and did not enclose the If

  For i = 0 To UBound(aItems)
     If Trim(aItems(i)) = Trim(newItem) Then found = True
     Exit For
  Next i

Which does not make sense and will kick out after the first item in the loop
The correct was

  For i = 0 To UBound(aItems)
        If Trim(aItems(i)) = Trim(newItem) Then
            found = True
             Exit For
        end if
  Next i

Pretty grim to have to split and loop on every item! If you have 100 items and no repeats then you're close to 1000 iterations

 

[MajP]

Pretty grim to have to split and loop on every item! If you have 100 items and no repeats then you're close to 1000 iterations

Sure I am all for creating efficient code, but when we need to start putting reality to things. It is not 1980. 1000 iterations is a fraction of a second. When start worrying about fractions of seconds as being "Grim" that is a kind of silly.

Moreover, I would think it is highly unlikely the OP is ever using a multi select to pick 100 items. If so he probably needs a better UI then a MS listbox. But lets assume he does. It is still a fraction of a second to do that.

But lets go more absurd. The OP spends 2 hours choosing 300 items from a multiselect list box. And wants to see all of these unique items in a textbox? OK. At this point I think they can wait two seconds to see their unusable textbox of 300 items.