return multiple values relating to 1 ID

[roasty_1]

Hi

Is there a way to return multiple values relating to a 1 ID?

File attached to show what I am getting at.

There are 2 columns, ID1 and Status, an ID can have more that one status.

How do I return all the statuses in seperate columns on another sheet.

i.e
ID1 - Status1 - Status2 - Status3 - Status4
1 - Answer1 - Answer2 - Answer3 - Answer4

A filter wont do because the data I am working on is well over 10,000 rows, I hope someone can help.

thanks

Roasty

[mrice]

Try this macro.

Sub Test()
Sheets("Data").Activate
Sheets("Summary Sheet").Rows("2:65536").Clear
For N = 2 To Cells(65536, 1).End(xlUp).Row
    If Application.CountIf(Sheets("Summary Sheet").Columns(1), Cells(N, 1)) = 0 Then
        Sheets("Summary Sheet").Cells(65536, 1).End(xlUp).Offset(1, 0) = Cells(N, 1)
        TargetRow = Sheets("Summary Sheet").Cells(65536, 1).End(xlUp).Row
    Else
        TargetRow = Sheets("Summary Sheet").Columns(1).Find(Cells(N, 1), , xlValues, xlWhole).Row
    End If
    Sheets("Summary Sheet").Cells(TargetRow, 256).End(xlToLeft).Offset(0, 1) = Cells(N, 2)
Next N
End Sub

[roasty_1]

thanks for this, works perfectly.

If I wanted to do this for more than whats in the file I attached earlier what would I need to change?

I am looking to do this on 30,000 rows, some IDs will have more than 50 occurances, so up to Status50

thanks

[mrice]

It should work for your larger data set as it is. You will just need to add some extra headers on your second sheet to go to the 50th.

It might take some time to get through all 30,000 so having a cup of coffee available is a good idea.

[roasty_1]

Cheers, thanks for your help

I'll get the coffee ready!!!