Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Monday 3 September 2012

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 AMOUNTYRPERIOD
2
20092009 Total
3
CATEGORYSUB_CATEGORYNL_ACCOUNTJan-09Feb-09Mar-09Apr-09
4
SalesProduct Sales4000 - 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 Sales4900 - 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
Purchases5000 - 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 Charges5100 - 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
1Sum of AMOUNTYRPERIOD
220092009200920092009 Total
3CATEGORYSUB_CATEGORYNL_ACCOUNTJan-09Feb-09Mar-09Apr-09Apr-09
4SalesProduct Sales4000 - Sales North(39,832.70)(37,076.73)(39,705.77)(47,349.18)(163,964.38)
5SalesProduct Sales4001 - Sales South                 -           100.00                  -      (1,330.00)     (1,230.00)
6SalesProduct Sales4002 - Sales Scotland                 -         (100.00)      (102.51)          50.00         (152.51)
7SalesProduct Sales4009 - Discounts Allowed          50.00                  -                    -                    -               50.00
8SalesProduct Sales Total4009 - Discounts Allowed(39,782.70)(37,076.73)(39,808.28)(48,629.18)(165,296.89)
9SalesOther Sales4900 - Miscellaneous Income                 -                    -           (55.00)           (5.03)           (60.03)
10SalesOther Sales4905 - Distribution and Carriage      (200.00)      (200.00)      (200.00)      (270.00)        (870.00)
11SalesOther Sales Total4905 - Distribution and Carriage      (200.00)      (200.00)      (255.00)      (275.03)        (930.03)
12Sales TotalOther Sales Total4905 - Distribution and Carriage(39,982.70)(37,276.73)(40,063.28)(48,904.21)(166,226.92)
13PurchasesPurchases5000 - Materials Purchased    3,713.90   19,062.54     7,845.83   14,824.21     45,446.48
14PurchasesPurchases5001 - Materials Imported                 -       4,023.00     7,159.00   12,551.00     23,733.00
15PurchasesPurchases5002 - Miscellaneous Purchases                 -                    -                    -       1,136.53        1,136.53
16PurchasesPurchases Total5002 - Miscellaneous Purchases    3,713.90   23,085.54   15,004.83   28,511.74     70,316.01
17PurchasesPurchase Charges5100 - Carriage                 -                    -                    -               1.26                1.26
18PurchasesPurchase Charges Total5100 - Carriage                 -                    -                    -               1.26                1.26
19Purchases TotalPurchase Charges Total5100 - 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-092009~Feb-092009~Mar-092009~Apr-092009 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:
RowColumnValue
Purchases Total~Purchase Charges Total~5100 - Carriage2009 Total~Apr-0970317.27
Purchases Total~Purchase Charges Total~5100 - Carriage2009~Apr-0928513
Purchases Total~Purchase Charges Total~5100 - Carriage2009~Feb-0923085.54
Purchases Total~Purchase Charges Total~5100 - Carriage2009~Jan-093713.9
Purchases Total~Purchase Charges Total~5100 - Carriage2009~Mar-0915004.83
Purchases~Purchase Charges Total~5100 - Carriage2009 Total~Apr-091.26
Purchases~Purchase Charges Total~5100 - Carriage2009~Apr-091.26
Purchases~Purchase Charges Total~5100 - Carriage2009~Feb-090
Purchases~Purchase Charges Total~5100 - Carriage2009~Jan-090
Purchases~Purchase Charges Total~5100 - Carriage2009~Mar-090
Purchases~Purchase Charges~5100 - Carriage2009 Total~Apr-091.26
Purchases~Purchase Charges~5100 - Carriage2009~Apr-091.26
Purchases~Purchase Charges~5100 - Carriage2009~Feb-090
Purchases~Purchase Charges~5100 - Carriage2009~Jan-090
Purchases~Purchase Charges~5100 - Carriage2009~Mar-090
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:


AmountCATEGORYSUB_CATEGORYNL_ACCOUNTYRPERIOD
1.26PurchasesPurchase Charges5100 - Carriage2009Apr-09
14824.21PurchasesPurchases5000 - Materials Purchased2009Apr-09
19062.54PurchasesPurchases5000 - Materials Purchased2009Feb-09
3713.9PurchasesPurchases5000 - Materials Purchased2009Jan-09
7845.83PurchasesPurchases5000 - Materials Purchased2009Mar-09
12551PurchasesPurchases5001 - Materials Imported2009Apr-09
4023PurchasesPurchases5001 - Materials Imported2009Feb-09
7159PurchasesPurchases5001 - Materials Imported2009Mar-09
1136.53PurchasesPurchases5002 - Miscellaneous Purchases2009Apr-09
-5.03SalesOther Sales4900 - Miscellaneous Income2009Apr-09
-55SalesOther Sales4900 - Miscellaneous Income2009Mar-09
-270SalesOther Sales4905 - Distribution and Carriage2009Apr-09
-200SalesOther Sales4905 - Distribution and Carriage2009Feb-09
-200SalesOther Sales4905 - Distribution and Carriage2009Jan-09
-200SalesOther Sales4905 - Distribution and Carriage2009Mar-09
-47349.18SalesProduct Sales4000 - Sales North2009Apr-09
-37076.73SalesProduct Sales4000 - Sales North2009Feb-09
-39832.7SalesProduct Sales4000 - Sales North2009Jan-09
-39705.77SalesProduct Sales4000 - Sales North2009Mar-09
-1330SalesProduct Sales4001 - Sales South2009Apr-09
100SalesProduct Sales4001 - Sales South2009Feb-09
50SalesProduct Sales4002 - Sales Scotland2009Apr-09
-100SalesProduct Sales4002 - Sales Scotland2009Feb-09
-102.51SalesProduct Sales4002 - Sales Scotland2009Mar-09
50SalesProduct Sales4009 - Discounts Allowed2009Jan-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

No comments:

Post a Comment