≡ Menu

Excel Pivot Table or Crosstab to Flat List

Excel Macro

I 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.

Cross Tab 1

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.

Excel table to flat list

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.

Cross Tab 2

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

Formula 3

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

Formula 4

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

Cross Tab 1

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.

Flat File 5

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
Sub CrossTabToList()
'written by Doctor Moxie

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
'I set mine as 2 as I want to put headings in row 1
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
'You will need to amend these to something appropriate for your 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 IN MY CROSS TAB
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 A2

'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

Here is a copy of file – you could copy the macro code from here: CrossTab-to-Flatfile

Doctor Moxie

Written by -

NHS Accountant with geeky tendencies - serial blogger on subjects varying from Excel, Raspberry Pi, productivity, allotment gardening and running. The NHSExcel blog is reserved for Excel topics.

Comments on this entry are closed.

  • Gary

    Absolutely Fantastic. Well done.
    Thank you

  • Kellen

    Very cool. Is there still a way to download the file itself?

  • http://www.doctormoxie.com Doctor Moxie

    Sorry about that. I have now corrected the link at the bottom of the post so you should be able to download the excel spreadsheet.

  • RIZWAN

    Great work…….SIR

    It help me a lot………..

  • Benjamin Macias

    You are a monster. This is AWESOME! For years we have told leadership that the reports they are requesting couldn’t be done because of the file format. This is going to make me look so good. THANK YOU.

    • http://www.doctormoxie.com Doctor Moxie

      Cool. Hope you get promoted!

  • David Powell

    A well-worded explanation of the problem, a great cleanup strategy*, and a nice motor – properly annotated – to generate the desired output!

    Just one thing on the sample spreadsheet: the button says, “Convert to Crosstab” – it does what we want, which is to convert to Flat File.

    Some might like to see the ‘higher order’ heading row from the crosstab appear first, but we’re talking about making a relational table, so that’s not really a consideration. The whole purpose is the flexibility to provide multiple views. It’s easier to work from the bottom heading row up, as you’ve done.

    (* I satisfied myself that you prepare the “Formulas to Fill Blanks” by using fill down. It might seem obvious, but I don’t often see formula display, and thought for a while that these were individually typed. Which would obviously defeat the purpose!)

    • http://www.doctormoxie.com Doctor Moxie

      Hi, Do you mean I should have brought in the Week header first, followed by plan/actual? That sounds like a good point and one that I will think about for the future. Thanks for the tip.

  • Pingback: Interactively get a flat file list from a crosstab or Pivot Table (Excel) | Tech Tools for Teachers()

  • https://davidwpowell.wordpress.com/2015/03/28/interactively-get-a-flat-file-list-from-a-crosstab-or-pivot-table-excel/ David Powell

    You may be interested in the work I’ve developed from yours – it generalises the code so that you can do the work interactively (or, if you prefer, using a set of plug-in constants). I provide plenty of credit for your excellent initiative and your test data! I use a paste-to-multiple-range technique to get the crosstab’s column labels transposed into place in one hit. I hope you are happy for me to share the URL: https://davidwpowell.wordpress.com/2015/03/28/interactively-get-a-flat-file-list-from-a-crosstab-or-pivot-table-excel/

    • http://www.doctormoxie.com Doctor Moxie

      That looks good – thanks for linking back to me.

  • Jodi Kidneigh

    Thank you very much.

  • uuutooo

    Even easier, 5 steps without VBA to achieve converting a crosstab to a flat file
    https://www.youtube.com/watch?v=pUXJLzqlEPk

    This is a time saver.

    • http://www.doctormoxie.com Doctor Moxie

      Thanks for that youtube link, it looks very interesting an I will be trying it out soon.

    • http://davidwpowell.wordpress.com David Powell

      Yes, worth knowing about! … However, I have Excel 2010, and can’t test one respondent’s claim that the function’s gone in 2013: “.. with excel 2013 the pivot for multiple consolidation ranges is no longer available. Any suggestions how this can be made to work on it. Any help is appreciated”

  • ahmad

    Many thanks for the link. This is what i have been looking for.
    Really appreciate your effort

  • Manish

    good post. however can you advise if we can keep forecast & actual data in seperate column.
    Thanks.