Excel formula to collect data

Question

I have this type of table in Excel:

   A | A1

   B | B1 

   A | A3

   A | A4

   B | B3

What I need is to create a new table where the data would look like this:

A | A1, A3, A4

B | B1, B3

Is there a formula or a way to do this other than manually?

Edit #1:

Basically what I want to do is described here http://www.extendoffice.com/documents/excel/802-excel-select-cells-based-on-criteria.html but the problem I am having is how you put that data in a new cell associated to the criteria you search for (All codes that contain "A" and "B" in our example).

score 0 · Answer 1 · edited Mar 20 '17 at 10:17

This does what you want. And if you need to know how to run a macro, see How do I add VBA in MS Office?

Sub doThis()

Dim row As Integer
row = 1

Range("E:E").Cells.Clear
Range("F:F").Cells.Clear

Do While (Range("A" & row).Value <> "")

    Dim currentA As String
    currentA = Range("A" & row).Value
    'check if the A col values exist in the E col
    Dim row2 As Integer
    row2 = 1

    Dim doesExist As Boolean
    doesExist = False

    Do While (Range("E" & row2).Value <> "")
        If (Range("E" & row2).Value = currentA) Then
            Range("F" & row2).Value = Range("F" & row2).Value & ", " & Range("B" & row).Value
            doesExist = True
            Exit Do
        End If
        row2 = row2 + 1
    Loop

    If Not doesExist Then
        Range("E" & row2).Value = Range("A" & row).Value
        Range("F" & row2).Value = Range("B" & row).Value
    End If



    row = row + 1
Loop


End Sub

Before

enter image description here

After the macro is run

enter image description here

As you can see, it doesn't just look for A and B so hopefully it won't be limited for your needs.

Excel formula to collect data

1 Answers1