Convert Crosstab to List

Sometimes the only information available to you is in crosstab format whereas, for whatever reason, you really need the information in list format.  This post describes a gereric approach to converting a crosstab to a list in Excel 2003. The data used for the example is the sort of thing you might get if someone copied a pivottable to a separate workbook and didn't include the underlying data.  For simple crosstabs there are other, simpler, ways of getting this done, but this approach should work for even quite complex crosstabs.

Here's the basic data as received:

	A	B	C	D	E	F	G	H
1	Sum of AMOUNT			YR	PERIOD
2				2009				2009 Total
3	CATEGORY	SUB_CATEGORY	NL_ACCOUNT	Jan-09	Feb-09	Mar-09	Apr-09
4	Sales	Product Sales	4000 - Sales North	(39,832.70)	(37,076.73)	(39,705.77)	(47,349.18)	(163,964.38)
5			4001 - Sales South	-	100.00	-	(1,330.00)	(1,230.00)
6			4002 - Sales Scotland	-	(100.00)	(102.51)	50.00	(152.51)
7			4009 - Discounts Allowed	50.00	-	-	-	50.00
8		Product Sales Total		(39,782.70)	(37,076.73)	(39,808.28)	(48,629.18)	(165,296.89)
9		Other Sales	4900 - Miscellaneous Income	-	-	(55.00)	(5.03)	(60.03)
10			4905 - Distribution and Carriage	(200.00)	(200.00)	(200.00)	(270.00)	(870.00)
11		Other Sales Total		(200.00)	(200.00)	(255.00)	(275.03)	(930.03)
12	Sales Total			(39,982.70)	(37,276.73)	(40,063.28)	(48,904.21)	(166,226.92)
13	Purchases	Purchases	5000 - Materials Purchased	3,713.90	19,062.54	7,845.83	14,824.21	45,446.48
14			5001 - Materials Imported	-	4,023.00	7,159.00	12,551.00	23,733.00
15			5002 - Miscellaneous Purchases	-	-	-	1,136.53	1,136.53
16		Purchases Total		3,713.90	23,085.54	15,004.83	28,511.74	70,316.01
17		Purchase Charges	5100 - Carriage	-	-	-	1.26	1.26
18		Purchase Charges Total		-	-	-	1.26	1.26
19	Purchases Total			3,713.90	23,085.54	15,004.83	28,513.00	70,317.27

First, we need to make sure we have all the necessary row and column data available. We need to fill in the blanks in the Category and Sub_Category rows and the Year column.

Select A4 to C19, press F5 (Edit | Go To...), left click Special..., left click the Blanks radio button and then left click on OK.  Next, press "=", press the Up arrow once and then press
Ctrl+Enter.  Now select D2 to H3, press F5 (Edit | Go To...), left click Special..., left click the Blanks radio button and then left click on OK.  Then, press "=", press the Left arrow once and then press Ctrl+Enter.  All the blank cells on detail rows and columns should now have the appropriate labels on them (don't worry about the Total rows and columns - we'll discard this data later).

The data now looks like this:

	A	B	C	D	E	F	G	H
1	Sum of AMOUNT			YR	PERIOD
2				2009	2009	2009	2009	2009 Total
3	CATEGORY	SUB_CATEGORY	NL_ACCOUNT	Jan-09	Feb-09	Mar-09	Apr-09	Apr-09
4	Sales	Product Sales	4000 - Sales North	(39,832.70)	(37,076.73)	(39,705.77)	(47,349.18)	(163,964.38)
5	Sales	Product Sales	4001 - Sales South	-	100.00	-	(1,330.00)	(1,230.00)
6	Sales	Product Sales	4002 - Sales Scotland	-	(100.00)	(102.51)	50.00	(152.51)
7	Sales	Product Sales	4009 - Discounts Allowed	50.00	-	-	-	50.00
8	Sales	Product Sales Total	4009 - Discounts Allowed	(39,782.70)	(37,076.73)	(39,808.28)	(48,629.18)	(165,296.89)
9	Sales	Other Sales	4900 - Miscellaneous Income	-	-	(55.00)	(5.03)	(60.03)
10	Sales	Other Sales	4905 - Distribution and Carriage	(200.00)	(200.00)	(200.00)	(270.00)	(870.00)
11	Sales	Other Sales Total	4905 - Distribution and Carriage	(200.00)	(200.00)	(255.00)	(275.03)	(930.03)
12	Sales Total	Other Sales Total	4905 - Distribution and Carriage	(39,982.70)	(37,276.73)	(40,063.28)	(48,904.21)	(166,226.92)
13	Purchases	Purchases	5000 - Materials Purchased	3,713.90	19,062.54	7,845.83	14,824.21	45,446.48
14	Purchases	Purchases	5001 - Materials Imported	-	4,023.00	7,159.00	12,551.00	23,733.00
15	Purchases	Purchases	5002 - Miscellaneous Purchases	-	-	-	1,136.53	1,136.53
16	Purchases	Purchases Total	5002 - Miscellaneous Purchases	3,713.90	23,085.54	15,004.83	28,511.74	70,316.01
17	Purchases	Purchase Charges	5100 - Carriage	-	-	-	1.26	1.26
18	Purchases	Purchase Charges Total	5100 - Carriage	-	-	-	1.26	1.26
19	Purchases Total	Purchase Charges Total	5100 - Carriage	3,713.90	23,085.54	15,004.83	28,513.00	70,317.27

Now we need to create a version of this crosstab with only one row field and one column field.  We'll use the tilde character as a delimiter so we can recover the original row and column data later.  In I4 type "=A4&"~"&B4&"~"&C4" and press enter. You should see Sales~Product Sales~4000 - Sales North in I4.  Copy the formula in I4 all the way down to I19. Then type "=D2&"~"&D3" in cell J3.  You should see 2009~Jan-09 in J3.  Copy the formula in J3 all the way across to N3.  Now type "=D4" in cell J4 and copy the formula all the way to N19.  This is what you should have in I3 to N19:

	2009~Jan-09	2009~Feb-09	2009~Mar-09	2009~Apr-09	2009 Total~Apr-09
Sales~Product Sales~4000 - Sales North	(39,832.70)	(37,076.73)	(39,705.77)	(47,349.18)	(163,964.38)
Sales~Product Sales~4001 - Sales South	-	100.00	-	(1,330.00)	(1,230.00)
Sales~Product Sales~4002 - Sales Scotland	-	(100.00)	(102.51)	50.00	(152.51)
Sales~Product Sales~4009 - Discounts Allowed	50.00	-	-	-	50.00
Sales~Product Sales Total~4009 - Discounts Allowed	(39,782.70)	(37,076.73)	(39,808.28)	(48,629.18)	(165,296.89)
Sales~Other Sales~4900 - Miscellaneous Income	-	-	(55.00)	(5.03)	(60.03)
Sales~Other Sales~4905 - Distribution and Carriage	(200.00)	(200.00)	(200.00)	(270.00)	(870.00)
Sales~Other Sales Total~4905 - Distribution and Carriage	(200.00)	(200.00)	(255.00)	(275.03)	(930.03)
Sales Total~Other Sales Total~4905 - Distribution and Carriage	(39,982.70)	(37,276.73)	(40,063.28)	(48,904.21)	(166,226.92)
Purchases~Purchases~5000 - Materials Purchased	3,713.90	19,062.54	7,845.83	14,824.21	45,446.48
Purchases~Purchases~5001 - Materials Imported	-	4,023.00	7,159.00	12,551.00	23,733.00
Purchases~Purchases~5002 - Miscellaneous Purchases	-	-	-	1,136.53	1,136.53
Purchases~Purchases Total~5002 - Miscellaneous Purchases	3,713.90	23,085.54	15,004.83	28,511.74	70,316.01
Purchases~Purchase Charges~5100 - Carriage	-	-	-	1.26	1.26
Purchases~Purchase Charges Total~5100 - Carriage	-	-	-	1.26	1.26
Purchases Total~Purchase Charges Total~5100 - Carriage	3,713.90	23,085.54	15,004.83	28,513.00	70,317.27

Select Data | PivotTable and PivotChart Report... | Multiple Consolidation Ranges and then click on Next.  Then select I will create the page fields and click on Next. Select cells I3 to N19 and click on Next. Select New worksheet and click on Finish. Double click on the grand total cell in the newly created PivotTable to extract what it sees as the underlying data in list format.  The data extracted should start off looking like this:

Row	Column	Value
Purchases Total~Purchase Charges Total~5100 - Carriage	2009 Total~Apr-09	70317.27
Purchases Total~Purchase Charges Total~5100 - Carriage	2009~Apr-09	28513
Purchases Total~Purchase Charges Total~5100 - Carriage	2009~Feb-09	23085.54
Purchases Total~Purchase Charges Total~5100 - Carriage	2009~Jan-09	3713.9
Purchases Total~Purchase Charges Total~5100 - Carriage	2009~Mar-09	15004.83
Purchases~Purchase Charges Total~5100 - Carriage	2009 Total~Apr-09	1.26
Purchases~Purchase Charges Total~5100 - Carriage	2009~Apr-09	1.26
Purchases~Purchase Charges Total~5100 - Carriage	2009~Feb-09	0
Purchases~Purchase Charges Total~5100 - Carriage	2009~Jan-09	0
Purchases~Purchase Charges Total~5100 - Carriage	2009~Mar-09	0
Purchases~Purchase Charges~5100 - Carriage	2009 Total~Apr-09	1.26
Purchases~Purchase Charges~5100 - Carriage	2009~Apr-09	1.26
Purchases~Purchase Charges~5100 - Carriage	2009~Feb-09	0
Purchases~Purchase Charges~5100 - Carriage	2009~Jan-09	0
Purchases~Purchase Charges~5100 - Carriage	2009~Mar-09	0

In D2 enter =A2&"~"&B2. Copy this all the way down to the last row of data. Select column D and Edit | Copy | Paste Special... | Values and click OK. Activate Data | Filter | Autofilter  on Column D and set the filter to only show rows that contain the word Total. Select all rows of data from row 2 to the end and right click Delete Row. Remove the autofilter. Select column D again. Then select Data |Text to Columns..., click on Delimited and click Next, put a ~ in the box beside Other and click Finish. To record the field names copy A3 to C3 in the original crosstab to D1 to F1, and D1 to E1 in the original crosstab to G1 to H1. Enter Amount in C1 (the original title of the value column in the crosstab). Delete Columns A and B. If you wish to remove some superfluous data you can filter on the Amount column to isolate zero values and delete those rows.  Here's the final tidied up data:

Amount	CATEGORY	SUB_CATEGORY	NL_ACCOUNT	YR	PERIOD
1.26	Purchases	Purchase Charges	5100 - Carriage	2009	Apr-09
14824.21	Purchases	Purchases	5000 - Materials Purchased	2009	Apr-09
19062.54	Purchases	Purchases	5000 - Materials Purchased	2009	Feb-09
3713.9	Purchases	Purchases	5000 - Materials Purchased	2009	Jan-09
7845.83	Purchases	Purchases	5000 - Materials Purchased	2009	Mar-09
12551	Purchases	Purchases	5001 - Materials Imported	2009	Apr-09
4023	Purchases	Purchases	5001 - Materials Imported	2009	Feb-09
7159	Purchases	Purchases	5001 - Materials Imported	2009	Mar-09
1136.53	Purchases	Purchases	5002 - Miscellaneous Purchases	2009	Apr-09
-5.03	Sales	Other Sales	4900 - Miscellaneous Income	2009	Apr-09
-55	Sales	Other Sales	4900 - Miscellaneous Income	2009	Mar-09
-270	Sales	Other Sales	4905 - Distribution and Carriage	2009	Apr-09
-200	Sales	Other Sales	4905 - Distribution and Carriage	2009	Feb-09
-200	Sales	Other Sales	4905 - Distribution and Carriage	2009	Jan-09
-200	Sales	Other Sales	4905 - Distribution and Carriage	2009	Mar-09
-47349.18	Sales	Product Sales	4000 - Sales North	2009	Apr-09
-37076.73	Sales	Product Sales	4000 - Sales North	2009	Feb-09
-39832.7	Sales	Product Sales	4000 - Sales North	2009	Jan-09
-39705.77	Sales	Product Sales	4000 - Sales North	2009	Mar-09
-1330	Sales	Product Sales	4001 - Sales South	2009	Apr-09
100	Sales	Product Sales	4001 - Sales South	2009	Feb-09
50	Sales	Product Sales	4002 - Sales Scotland	2009	Apr-09
-100	Sales	Product Sales	4002 - Sales Scotland	2009	Feb-09
-102.51	Sales	Product Sales	4002 - Sales Scotland	2009	Mar-09
50	Sales	Product Sales	4009 - Discounts Allowed	2009	Jan-09

This data is good to go for whatever sort of different analysis you wish to do with it.

With a bit of planning you can set the basic formulae up in such a way that they'll work with any crosstab you paste into the worksheet (within maximum row and column field number limits you decide upon).

Comments or suggestions for improvement welcome