Consolidate list into teams across columns...

[largeselection]

Sorry for the cryptic title:

Basically I have a list as shown in "INPUT" below and I want to have it automatically convert into the "OUTPUT" below.

It basically separates out the ID#s under their respective team names.

The way I thought I could accomplish it would be to have it automatically pivot table with team across the top, and ID # down the rows. then use ID # as the data. This would separate the ID#s into their team columns, but there would be blank spaces. So then I'd have to put in code to sort by each team name, then the next team name, then the next to group all the spaces at the bottom.

I'm sure there's a better way than all that sorting etc.

Here's the examples:

Excel Workbook
	A	B	C	D	E	F
1	INPUT			OUTPUT
2	ID #	Team		Red	Blue	Green
3	11111	Red		11111	11112	11113
4	11112	Blue		11114	11117	11116
5	11113	Green		11115	11118	11119
6	11114	Red		11120	11124	11121
7	11115	Red		11122		11125
8	11116	Green		11123
9	11117	Blue
10	11118	Blue
11	11119	Green
12	11120	Red
13	11121	Green
14	11122	Red
15	11123	Red
16	11124	Blue
17	11125	Green
18
19
20	Example
21		Red	Blue	Green
22	11111	11111
23	11112		11112
24	11113			11113
25	11114	11114
26	11115	11115
27	11116			11116
28	11117		11117
29	11118		11118
30	11119			11119
31	11120	11120
32	11121			11121
33	11122	11122
34	11123	11122
35	11124		11124
36	11125			11125
Sheet3

 

[largeselection]

I tried two methods:

1- I put code in to copy/paste the pivot table into another sheet
2- on another sheet I had formulas which pulled the data from the pivot table

then in the code i pasted as values and put in some code to delete the blank cells. I tried using the code below:

Dim xCol As Integer
    For xCol = 2 To 64
        Range(Cells(3, xCol), Cells(802, xCol)). _
        SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
        
        
        
        
        Next xCol

however I keep getting a 400 error with no description. Not sure what the problem is