Excel - get smaller table of unique preserving properties of original table

Question

I have a table that's similar to the following (input):

_element___|_color__
 494       | red
 592       | blue
 493       | red
 139       | green
 393       | blue
 496       | black

And I want to get a table that looks like this (output):

_color_____|_elements__
 red       | 493, 494
 blue      | 393, 592
 green     | 139
 black     | 496

I would also accept an answer that gets the following output:

_color_____|_element__|_element__|_element__|
 red       | 493      | 494      |          |
 blue      | 393      | 592      |          |
 green     | 139      |          |          |
 black     | 496      |          |          |

I know from this answer how to get the column "color" on the left done. I don't know how to make the column "elements" on the right done - order doesn't matter.

I prefer without macros, but with macros is fine too! - I don't know enough Excel to judge which is best. I also need a better title for this question.

___EDIT____

Looking through the code of the answer by @Gary's Student, I made this function below:

Function GetElement(elements As Range, colors As Range, color As Range) As String
    Dim i As Long
    Dim Nels As Long: Nels = elements.Count + 1
    Dim str As String: str = ""

    If IsError(Cells(color.Row, color.Column).Value) Then
        GetElement = ""
        Exit Function
    End If

    For i = 2 To Nels
            If Cells(color.Row, color.Column).Value = Cells(i, colors.Column).Value Then
                If str = "" Then
                    str = Cells(i, elements.Column).Value
                Else
                    str = str & ", " & Cells(i, elements.Column).Value
                End If
            End If
    Next i

    GetElement = str
End Function

Using this function on the cells of the column at the right of "Color", using f4 to lock both first and second argument to the range of the columns "Element" and the right "Color" got the correct

score 0 · Accepted Answer · answered May 14 '15 at 12:50

If we start with:

enter image description here

and run this short macro:

Sub TableOrganizer()
  Dim st As String, i As Long, N As Long
  Dim M As Long, j As Long
  Range("B:B").Copy Range("C1")
  Range("C:C").RemoveDuplicates Columns:=1, Header:=xlYes
  N = Cells(Rows.Count, "C").End(xlUp).Row
  M = Cells(Rows.Count, "A").End(xlUp).Row

  For i = 2 To N
    st = Cells(i, "C").Value
    t = ""
    For j = 2 To M
      If st = Cells(j, "B").Value Then
        t = t & "," & Cells(j, "A").Value
      End If
    Next j
    Cells(i, "D").Value = "'" & Mid(t, 2)
  Next i
End Sub

We will end up with:

enter image description here

Excel - get smaller table of unique preserving properties of original table

1 Answers1