Consolidate list into teams across columns...

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
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
ABCDEF
1INPUTOUTPUT
2ID #TeamRedBlueGreen
311111Red111111111211113
411112Blue111141111711116
511113Green111151111811119
611114Red111201112411121
711115Red1112211125
811116Green11123
911117Blue
1011118Blue
1111119Green
1211120Red
1311121Green
1411122Red
1511123Red
1611124Blue
1711125Green
18
19
20Example
21RedBlueGreen
221111111111
231111211112
241111311113
251111411114
261111511115
271111611116
281111711117
291111811118
301111911119
311112011120
321112111121
331112211122
341112311122
351112411124
361112511125
Sheet3
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
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:

Code:
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
 

Forum statistics

Threads
1,136,334
Messages
5,675,178
Members
419,552
Latest member
jsanjur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top