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:

Back to the data sheet like this:

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.

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

Tags: excel, list, macro, Macros, Pivot Table, table, used range, VBA