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

You may also like

  • JP

    Thanks for posting this one. Any idea how you would do this if the first three columns were like your name above? For example, A1 = Province, B1 = District, C1 = Site. I need all three of these to be copied down and the data from columns D – N(varied) to be copied from cross-tab into a flat table. How would you alter the above to accomplish this task?

  • Thomas

    Thanks for great post and sample file – I needed exactly this solution, which was really helpful!

  • AL

    This is a great post. I have the same question as JP. I need the first three columns from my cross tab to carry, with variations for the remaining columns.

  • R Shephard

    Fantastic! Saved me hours of work. Really appreciated the sample file and explanations in code, so that I could modify the macro to suit my application.
    rs

  • Pravee

    Any Idea How we can write same Macro Outside th Excel.
    This we wrote inside the excel but i want to write everything in a .vbs file and run the same concept to make cross tab to tabular.
    How can we do that??

  • Mark E

    Cheers, prevented a lot of manual work converting tabled dates and quantities into a flat list for data import. Very useful.

  • Kosh

    Excelent! Would be better if I could work out how to do it with multiple columns as above, but still brilliant as it is. Many thanks for taking the time to put it up.

  • George

    Thanks – this is exactly what I was looking for. I have a similar list that I have been doing manually but this is MUCH FASTER!
    Now that I have this solution I have one more thing that I am doing manually that I know could be done at the same time if I could write code:
    I need to concatenate two fields into a 3rd field to create a unique name. In your example, if I wanted to join the Name and Grade fields together into a 4th column named “NameGrade”. How would you write this into your code?
    thanks again!
    George

  • Marco

    Thank you, your macro saved me a huge amount of time !

  • Miles

    I spent hours searching for a macro to do this and eventually ended up installing a plug in that did the trick. However, I couldn’t call the plugin routine in a macro, so it didn’t work out for me. Therefor, I am so happy I found your page. This is exactly what I was looking for and I am able to call it as a submacro to automate a reporting process! Thanks so much for your hard work! This is genius!

  • Miles

    Do you have any idea how i’d change the macro so that it will not return blank values in the crosstab? I have a crosstab report I need to run this on daily that has 20000 rows and 90 columns. Therefore it tried to create 1,800,000 new rows after running the macro. once deleting the blank values there are only about 50,000 rows. Therefore is it possible to count the number of non-blank values in the crosstab and then create that many rows for those values instead of just counting the number of column headers? I hope thhat makes sense.

  • Miles

    Never mind, I figured out a way to do it.

    Thanks again!