Excel Pivot Table or CrossTab to Flat List

flatlist1

Excel to CrossTabI often find myself struggling to deal with data that has been rendered almost unusable by the data provider who has converted it to a cross tab format. Usually they think they are helping and have probably started with a nice flat list then spent ages formatting it so the data spreads out across the columns. Sometimes they have achieved this by putting the data into a pivot table but then have pasted the table as values and removed all links to the underlying data. Very helpful.

Yesterday I had to deal with a data file that looked a little like this only  it spread out across 455 columns and was frankly useless.

 

I wrote a post a while back that demonstrated how to convert a simple cross tab back to a manageable data list but I wanted to expand this with a slightly more complex example which had more field headings.

So starting with the table above which was probably a pasted copy from a pivot table I applied a little bit of formatting to achieve the following starting table.

What I have done is remove the grouping that the pivot table applies and ensured that I have the relevant  week number, project and department details against each name

I often wish to convert pivot table outputs back into a data file and the way I fill in the blanks caused by grouping data is to prepare a sheet with formulas that copy values from above (or the side as appropriate):

I then copy the pivot table data and then use the paste special command selecting paste values and skip blanks as the options.

This results in the following output, which I then copy and paste over itself as values, to give me the desired structure.

 

Having spent a bit of time on the structure of my starting file I can run the macro that will work through each row and transpose the data from a columnar layout to one based on rows.

The advantage of this format is that I can now create my own pivot tables and cut the data as I see fit to produce multiple summary formats according to my audience.

 

Here’s the macro I use:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
Sub CrossTabToList()

Dim wsCrossTab As Worksheet
Dim wsList As Worksheet
Dim iLastCol As Long
Dim iLastRow As Long
Dim iLastRowList As Long
Dim rngCTab As Range 'Used for range in Sheet1 cross tab sheet
Dim rngList As Range 'Destination range for the list
Dim ROW As Long

Set wsCrossTab = Worksheets("Sheet1") 'AMEND TO SHOW SHEET NUMBER WITH THE CROSS TAB
Set wsList = Worksheets.Add

'Find the last row in Sheet1 with the cross tab
iLastRow = wsCrossTab.Cells(Rows.Count, "A").End(xlUp).ROW

Set the initial value for the row in the destination worksheet
iLastRowList = 2

Find the last column in Sheet1 with the cross tab
iLastCol = wsCrossTab.Range("A2").End(xlToRight).Column

'SET THE HEADING TITLES IN THE LIST SHEET
wsList.Range("A1:F1") = Array("NAME", "PROJECT", "TYPE", "PLAN/ACTUAL", "WEEK", "HOURS")

'Start looping through the cross tab data

For ROW = 3 To iLastRow 'START AT ROW 3 AS THIS IS WHERE DATA BEGINS
Set rngCTab = wsCrossTab.Range("A" & ROW, "C" & ROW)'initial value A3 SETS THE RANGE TO

'INCLUDE ALL STATIC DATA - IN THIS CASE NAME, PROJECT, TYPE
Set rngList = wsList.Range("A" & iLastRowList) 'initial value A3

'Copy individual names in Col A (A3 initially) into as many rows as there are data columns
'in the cross tab (less 3 for Col A-C).
rngCTab.Copy rngList.Resize(iLastCol - 3)

'SELECT THE HEADING ROW WITH FORECAST/ACTUAL
'Move up ROW (INITIALLY 3) rows less TWO and across 3 columns (using offset function). Copy.
rngCTab.Offset(-(ROW - 2), 3).Resize(, iLastCol - 3).Copy

'Paste transpose to columns in the list sheet alongside the static data
rngList.Offset(0, 3).PasteSpecial Transpose:=True

'SELECT THE ROW WITH THE WEEK NUMBERS
'Move up ROW (INITIALLY 3) rows less ONE and across 3 columns (using offset function). Copy.
rngCTab.Offset(-(ROW - 1), 3).Resize(, iLastCol - 3).Copy

'Paste transpose to columns in the list sheet alongside the static data
rngList.Offset(0, 4).PasteSpecial Transpose:=True

'Staying on same row (3 initially) copy the data from the cross tab
rngCTab.Offset(, 3).Resize(, iLastCol - 3).Copy

'Past transpose as column in list sheet
rngList.Offset(0, 5).PasteSpecial Transpose:=True

'Set the new last row in list sheet to be just below the last name copied
iLastRowList = iLastRowList + (iLastCol - 3)

'increment ROW by 1
Next ROW

End Sub

I’m afraid the VBA code isn’t rendering very well – you’ll have to replace all the ” and ‘ marks before the macro editor will recognise the code.
Here is a copy of file – you could copy the macro code from here: Excel to CrossTab.xls

Excel CrossTab Table to Flat List

I had a query on my sumproduct tutorial that was effectively asking if I could work backwards from an excel crosstab or data table to get back to the simple flat list.

Converting a table like this:

sumproduct

Back to the data sheet like this:

sumproduct

If you didn’t have to do this sort of task very often you could get by with a pivot table solution and a few stages of formatting but it would only work if your data values were numbers that could be summed in the pivot.

If you need to convert Excel data tables (or crosstabs) into flat lists on a regular basis then you will need a macro.

I have prepared a workbook that demonstrates both the pivot table and macro solution and you can download it from here:
ExcelTabletoFlatList.xls

The following macro does the trick and utilises the VBA I introduced in my last post on consolidating worksheets, to determine the last used row and last used column.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
Sub CrossTabToList()

Dim wsCrossTab As Worksheet
Dim wsList As Worksheet
Dim iLastCol As Long
Dim iLastRow As Long
Dim iLastRowList As Long
Dim rngCTab As Range 'Used for range in Sheet1 cross tab sheet
Dim rngList As Range 'Destination range for the list
Dim I As Long

Set wsCrossTab = Worksheets("Sheet1")
Set wsList = Worksheets.Add

'Find the last row in Sheet1 with the cross tab
iLastRow = wsCrossTab.Cells(Rows.Count, "A").End(xlUp).Row

'Set the initial value for the row in the destination worksheet
iLastRowList = 2

'Find the last column in Sheet1 with the cross tab
iLastCol = wsCrossTab.Range("A1").End(xlToRight).Column

'Create a new sheet and set the heading titles
wsList.Range("A1:C1") = Array("NAME", "GRADE", "VALUE")

'Start looping through the cross tab data

For I = 2 To iLastRow

Set rngCTab = wsCrossTab.Range("A" & I) 'initial value A2
Set rngList = wsList.Range("A" & iLastRowList) 'initial value A2

'Copy individual names in Col A (A2 initially) into as many rows as there are data columns
'in the cross tab (less 1 for Col A).
rngCTab.Copy rngList.Resize(iLastCol - 1)

'Move up a I rows less one and across one column (using offset function) to select heading row. Copy.
rngCTab.Offset(-(I - 1), 1).Resize(, iLastCol - 1).Copy

'Paste transpose to columns in the list sheet alongside the names
rngList.Offset(0, 1).PasteSpecial Transpose:=True

'Staying on same row (2 initially) copy the data from the cross tab
rngCTab.Offset(, 1).Resize(, iLastCol - 1).Copy

'Past transpose as column in list sheet
rngList.Offset(0, 2).PasteSpecial Transpose:=True

'Set the new last row in list sheet to be just below the last name copied
iLastRowList = iLastRowList + (iLastCol - 1)

'increment I by 1
Next I

End Sub

Sub Totals and Pivot Table Reports

Inserting subtotals in pivot tables

Removing sub totals from pivot table reports

When you add detail to pivot table rows, Excel will automatically add a subtotal row. This can very quickly litter your pivot table with unnecessary detail.

Pivot table littered with subtotals

I remove the unwanted subtotals by right clicking on one of the totals and selecting “Hide”.

Removing subtotals from pivot table

Removing subtotals from pivot table

Adding sub totals to pivot table reports

Having removed all my unwanted subtotals, I will typically decide to rearrange the ordering in my pivot table and find I am missing a vital subtotal. It is possible to reapply subtotaling though – so don’t panic.

Following on from the examples above, it may be preferable to have the Cost Centre description appearing first rather than the cost centre code (which means nothing to anyone outside of finance).

Inserting subtotals in pivot tables

To add the subtotal against this description, right click in any of the description values, choose “Field Settings” and then make sure the radio box labeled “Automatic” is selected.

Inserting subtotals in pivot tables

Inserting subtotals in pivot tables

This enables you to add and remove subtotals at will.

Inserting subtotals in pivot tables