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

SSL IMAP email certificate error message

This is somewhat "off-piste" but I've been so incredibly frustrated by this issue I thought I'd write to: (1) see if anyone can enlighten me; or, (2) maybe be of help to anyone else suffering the same frustrations. 

Security is a good thing, right?  So, with a vague notion that I should activate SSL on my email accounts, I set about doing so on the incoming and outgoing mail servers I've connected my Outlook account to. I use IMAP for incoming mail.  

The instructions I had said that for the incoming mail server I should use server.myhosting.co.uk (no mention of port number). For the outgoing mail server I should use server.myhosting.co.uk (port 465).

When I opened MS Outlook 2003 I had to respond to three identical Internet Security Warning messages:

"The server you are connected to is using a security certificate that could not be verified.
A certificate chain processed, but terminated in a root certificate which is not trusted by the trust provider.
Do you want to continue using this server? Yes/No"

I asked my email provider about this and the response I got was: "You just need to "trust" the certificate in Outlook and then it shouldn't ask you again. Please refer to your Outlook support documentation on how to do this."

To cut a long story short, I couldn't find "the certificate" anywhere (am I really that dim?). Without knowing where or how the certificate could be located I couldn't "trust" it. I couldn't find anything in the Outlook help that was even remotely helpful.

After some scratching around the web looking for answers to "certificate" problems I found something that suggested that I needed to "obtain the certificate" by visiting the mail server using the designated port and "installing the certificate". 

I typed https://server.myhosting.co.uk:465 as a URL.  I got:

There is a problem with this website's security certificate.

The security certificate presented by this website was not issued by a trusted certificate authority.

Security certificate problems may indicate an attempt to fool you or intercept any data you send to the server.

We recommend that you close this webpage and do not continue to this website.

Click here to close this webpage.

Continue to this website (not recommended).

More information

When I clicked on "Continue to this website" I got a certificate error message in the box beside my URL box.  I clicked on it and clicked on "View certificates" and then clicked Install certificate... | Next | Next | Finish | Yes | OK | OK in sequence.  You need to take your own decision about clicking on Yes in the previous sequence - it's your decision, not mine.

Noting that Outlook set my port number to 993 for the incoming IMAP setting I tried the same approach there using https://server.myhosting.co.uk:993 but got a page not found error. 

What to do? I decided I'd try the incoming mail port used for POP3 protocols (995). So I typed https://server.myhosting.co.uk:995 as the URL.  I thought I was out of luck as it took forever to connect - but then it did.  I "installed the certificate" and re-booted.  It did the trick.
 
I can't believe that coming to something that appears to work has been as random as what I've done.  I feel that I just don't "get" how this is supposed to work. I'd love a simple explanation of the theory of SSL email so I could have more confidence that all of this isn't going to come back and bite me again when the certificates expire in a year's time.

I'd love to hear some other experiences on this.