Keep duplicates but distinguish them

Question

I got pretty simple question. I will simplify the example. I got excel sheet with 2 columns. In the first column I want to keep duplicated items, lets say 'apple' . But in another column they differ, lets say 'green', 'red' etc.

I have userform with combobox and textbox. Combobox is related to column with apples, textbox to that with different values - colors. And when I choose item in Combobox it will show relevant value in textbox. But every time I choose Apple in combobox, it will show only the last option - red in this case.

Is There a way to treat this?

Those items in column A need to be the same, it can't be apple1, apple2 etc.

And There will be more items in A columns and all can be duplicates, like apple, apple, pear, orange, orange, orange ...

It's terrible example but I Hope it makes sense

Pls help :)

Private Sub Userform_click() 
Dim fruit As String
Dim color As String

row_number = 1

Do

fruit = Sheets("List1").Range("A" & row_number)
color = Sheets("List1").Range("B" & row_number)

If ComboBox1 = fruit Then
    TextBox1 = color
End If

row_number = row_number + 1

Loop Until fruit = ""

End Sub



Private Sub CommandButton1_Click()
Dim fruit As String

row_number = 2

With ComboBox1
    .Clear
End With

Do

fruit = Sheets("List1").Range("A" & row_number)

If fruit <> "" Then
    With ComboBox1
        .AddItem fruit
    End With
End If

row_number = row_number + 1

Loop Until fruit = ""

End Sub

It is just a simple example.

Everytime I search for this I find only 'how to remove duplicates' :(.

EDIT:

OK guys one more thing, here is a picture attached where is stated what I want to do (again), but could you please send me complete code (if it is not too complicated?). I still can't figure it out. (Newbie)

CODE:

Private Sub CommandButton1_Click()
Dim fruit As String
Dim number As String

row_number = 1

With ComboBox1
    .Clear
End With

Do

number = Sheets("List1").Range("A" & row_number)
fruit = Sheets("List1").Range("B" & row_number)

If TextBox1 = number Then
    With ComboBox1
        .AddItem fruit
    End With
End If

row_number = row_number + 1

Loop Until fruit = ""

End Sub

Private Sub ComboBox1_Change()
Dim fruit As String
Dim fshape As String
Dim fcolor As String

row_number = 1

Do

fruit = Sheets("List1").Range("B" & row_number)
fshape = Sheets("List1").Range("C" & row_number)
fcolor = Sheets("List1").Range("D" & row_number)

If ComboBox1 = fruit Then
    TextBox2 = fshape
    TextBox3 = fcolor
End If

row_number = row_number + 1

Loop Until fruit = ""

End Sub

Answer 1

Use a multi column combo box and fill the text box from the second column of the combo box. Otherwise you cannot distinguish Apple from Apple.

See Inserting values into multicolumn combobox for more details about how to fill a multi column box.

Use the following code to fill the text box from the combo box selection:

Private Sub ComboBox1_Change()
    Me.TextBox1.Text = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
End Sub

If you want the second column being invisible just set its width to 0.

Another easy way to fill your ComboBox is to use the RowSource property:

Private Sub UserForm_Initialize()
    Dim DataRange As Range
    Set DataRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:D7")

    With Me.ComboBox1
        .RowSource = DataRange.Address
        .ColumnCount = 4
        .ColumnWidths = "40;50;50;100"
        .ColumnHeads = True
    End With
End Sub

Note that the value of the ComboBox will automatically be the first column of your data. If you need to change it re-order your data columns.

Answer 2

you could stay with a single column combo box, too

here's the whole code in your Userform code pane:

Option Explicit

Dim fruits As Variant, colors As Variant

Private Sub ComboBox1_Change()
    If Me.ComboBox1.ListIndex <> -1 Then Me.TextBox1.Value = colors(Me.ComboBox1.ListIndex + 1, 1)
End Sub


Private Sub UserForm_Initialize()
    With Sheets("List1")
        With .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
            fruits = .Value
            colors = .Offset(, 1).Value
        End With
    End With
    Me.ComboBox1.List = fruits
 End Sub

where you need no button to press as textbox gets filled at every combobox change

Answer 3

Here is a very simple example on how you could tackle the issue. I would suggest using a ComboBox for the category (Apple, Pear, Orange) and a ListBox for it's different products.

Example data like:

Userform Like:

Code Like:

Private Sub UserForm_Initialize()
    Me.ComboBox1.List = Array("Apple", "Pear")
End Sub

Private Sub ComboBox1_Change()

With Sheet1 'Change according to your own sheets CodeName.
    Dim lr As Long: lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    Dim cl As Range, rng As Range: Set rng = .Range("A1:B" & lr)
    rng.AutoFilter 1, Me.ComboBox1.Value
    Dim arr As Variant: arr = rng.Columns(2).Offset(1).Resize(rng.Columns(2).Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    Me.ListBox1.List = arr
    rng.AutoFilter
    Me.TextBox1.Value = ""
End With

End Sub

Private Sub ListBox1_Click()
    Me.TextBox1.Value = Me.ListBox1.Value
End Sub

Result:

---

The way you currently try to work is quite inefficient, but below is how you could tackle that way too (however @Peh his proposal is more efficient)

Other type of UserForm:

New code:

Private Sub UserForm_Initialize()

With 'Change according to your sheets CodeName
    Dim lr As Long: lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    Dim arr As Variant: arr = .Range("A2:A" & lr)
    Me.ComboBox1.List = arr
End With

End Sub

Private Sub ComboBox1_Change()

With Sheet1 'Change according to your sheets CodeName
    Me.TextBox1.Value = .Cells(Me.ComboBox1.ListIndex - 1, 2).Value
End With

End Sub