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.