Pivot: Splitting a cell for a table with unique rows

[pookgai]

I have a table with unique rows (column A is unqiue identifier), one of the columns in the table has data which I want to pivot on, however, each cell in that column carries more than one criteria, So it looks something like this:

001 | E1
002 | E1
003 | E1,E2,E3
004 | E2
005 | E3

So would like to run a pivot which would group by column B . You can see my problem as the pivot would show "E1,E2,E3" as one value rather than being clever enough to recognise and group them under the correct groups.

Any ideas how I could work around this? I have quite a few other columns and the table runs into over a thousand unique rows.

 

[CWatts]

I have some code that could work for you, but you'll have to modify it a bit or use the following method to make it work for your needs. (I don't have time to rewrite it at the moment.) Also, if you mean "table" like an Excel 2007+ table, you might have to convert it to a range before doing this.

Take your data, and perform a text-to-columns so that each comma value is separated in it's own column. It should look like this:

Sheet1

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:30px;"><col style="width:22px;"><col style="width:22px;"><col style="width:22px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:center; ">001</td><td style="text-align:center; ">E1</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:center; ">002</td><td style="text-align:center; ">E1</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:center; ">003</td><td style="text-align:center; ">E1</td><td style="text-align:center; ">E2</td><td>E3</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:center; ">004</td><td style="text-align:center; ">E2</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:center; ">005</td><td style="text-align:center; ">E3</td><td>
</td><td>
</td></tr></tbody></table>

Then, highlight the data, A1 to D6 (or whatever the furthest is). Its important that you have the blank space above it highlighted My macro process it and format it in this way:

Sheet1

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:30px;"><col style="width:30px;"><col style="width:22px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:center; ">001</td><td>
</td><td style="text-align:center; ">E1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:center; ">002</td><td>
</td><td style="text-align:center; ">E1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:center; ">003</td><td>
</td><td style="text-align:center; ">E1</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:center; ">004</td><td>
</td><td style="text-align:center; ">E2</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:center; ">005</td><td>
</td><td style="text-align:center; ">E3</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:right; ">001</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:right; ">002</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="text-align:right; ">003</td><td>
</td><td style="text-align:center; ">E2</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="text-align:right; ">004</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style="text-align:right; ">005</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style="text-align:right; ">001</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style="text-align:right; ">002</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td style="text-align:right; ">003</td><td>
</td><td style="text-align:center; ">E3</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td style="text-align:right; ">004</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">16</td><td style="text-align:right; ">005</td><td>
</td><td>
</td></tr></tbody></table>

You can then delete column B and run your pivot off those values keeping the blanks in there, or you can sort by column B (which was column C before you deleted blank column B) and delete the block of blanks and run you pivot.

The Macro is as follows:

Sub Data_To_Pivot()
Dim Rng As Range
Set Rng = Selection
Dim Count As Integer
Count = 1
Columns(2).Insert
Columns(2).Insert
Dim CurRow As Integer
Dim CurCol As Integer
Dim ToDo As Integer
Dim SourceRow As Integer
Dim SourceCol As Integer
ToDo = Rng.Columns.Count - 2
SourceRow = 2
CurRow = Rng.Rows.Count + 1
Do While ToDo > 2
    Cells(CurRow, 1) = Rng.Cells(SourceRow, 1)
    SourceRow = SourceRow + 1
    CurRow = CurRow + 1
    If SourceRow > Rng.Rows.Count Then ToDo = ToDo - 1
    If SourceRow > Rng.Rows.Count Then SourceRow = 2
Loop
CurRow = 2
CurCol = 2
SourceRow = 1
SourceCol = 4
Do While SourceCol < Rng.Columns.Count + 2
Cells(CurRow, CurCol).Value = Cells(SourceRow, SourceCol).Value
CurRow = CurRow + 1
Count = Count + 1
If Count = Rng.Rows.Count Then SourceCol = SourceCol + 1
If Count = Rng.Rows.Count Then Count = 1
Loop
SourceCol = 4
SourceRow = 2
CurRow = 2
Do Until SourceCol = Rng.Columns.Count + 2
    Cells(CurRow, 3).Value = Cells(SourceRow, SourceCol)
    SourceRow = SourceRow + 1
    CurRow = CurRow + 1
    If SourceRow > Rng.Rows.Count Then SourceCol = SourceCol + 1
    If SourceRow > Rng.Rows.Count Then SourceRow = 2
Loop
Range("D1:DD10000").Clear
Columns("B:C").HorizontalAlignment = xlCenter
Columns("A:C").EntireColumn.AutoFit
Range("A1").Select
End Sub

It's not pretty, but it should work for you. Hope it helps!

 

[hiker95]

pookgai,

What should this look like after the macro?
001 | E1
002 | E1
003 | E1,E2,E3
004 | E2
005 | E3

 

[pookgai]

Cheers. It does what I asked..but i didn't tell the whole story

So the macro works and so i can run a pivot which gives me:

E1
- 001
- 002
- 003
E2
- 004
E3
- 004

The problem is that I have other columns against each unique row that I want to keep, but the macro crunches all columns into column C and also chops off data depending on whether included it in my seleciton or not before i hit the macro .

So the table might look something like this

001 | E1 | Text 1 | John
002 | E1 | Text 2 | Jim
003 | E1,E2,E3 | Text 3 | John
004 | E2 | Text 4| Bob
005 | E3 | text 5 | Jim

The macro correctly gives me a pivot so i can pivot on the first two columns, but the subsequent columns are lost.

I guess i could run some kind of lookup ..perhaps a vlookup which references the unique id to an earlier version of the table before the macro to return the missing columns, but would be neat if the macro could factor in additional columns

 

[hiker95]

pookgai,

So the table might look something like this

I still do not understand what your original data looks like (column and row positions), and what the results should look like (column and row postions).


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

 

[CWatts]

Just disable the line that clears the data by putting an apostrophe in front of it:

'Range("D1:DD10000").Clear