Microsoft Office UK/US language confusion

I've been having a nightmare with dates and spelling showing as US format. Here are my Windows settings:

and, here are my Office settings:

I wondered which sort of English that might be and so clicked on the “Add a Language” button to add “English (United Kingdom)”. This resulted in a changed display where “English” was replaced by “English (United Kingdom)”. 

I think most systems will be set to “Match Microsoft Windows [English]”. In spite of saying it matches Microsoft Windows, it doesn’t actually match the UK bit of the Windows setting and, instead, uses US English! 

Now that it reads “Match Microsoft Windows [English (United Kingdom)]”, I'm getting what I want. How confusing is that?

Excel High CPU Usage

My laptop sounded like it was working hard so I opened up Task Manager in Windows to see what was going on and, to my surprise, I found that Excel was reporting greater than 50% CPU usage. Permanently it would seem. After a lot of blind alleys I eventually discovered that the zoom setting on the tabs seemed to be the critical factor. At 100% zoom on all 12 tabs the CPU problem persisted. Setting them all to 90% zoom seemed to make the problem marginally worse (approaching 60% CPU usage). Setting them all to 80% zoom seemed to make the problem go away. 

Searching for what might be the issue, I noted that freeze panes was active on one of the sheets such that the scrolling part of the screen was only just visible at 80% zoom. Trial and error determined that the scrolling part of the screen disappeared at 82% zoom and that was the exact zoom level that the CPU usage problem occurred. I removed freeze panes from the sheet and set the ScrollArea in worksheet properties to achieve the same sort of "locked area" effect that freeze panes was used to create and had a usable workbook again.

Excel 2016 version 1612 change

Following the release of the Microsoft Office 365 update to Excel 2016 version 1612 (January 25, 2017), there has been a change in Excel’s default behaviour in relation to PivotTable subtotals when a new field is created following a Group operation. Until now, the new field has been created with the subtotals set to “None”. Now, the new field will be created with the subtotals set to “Automatic”.

I haven’t checked whether the same change occurs with subscription versions of Excel 2013.

Windows 10 Anniversary Update (1607) and problems with Excel

Some Microsoft Windows 10 users have recently been reporting that their Microsoft Excel either crashes or hangs regularly, particularly after the Windows 10 Anniversary Update to version 1607 in August 2016.

The issue can often be traced to printer driver incompatibilities with Windows 10 which affect the correct operation of Excel. Correct operation of Excel can usually be restored by temporarily setting your "Default" printer driver to either "Microsoft Print to PDF" or "Microsoft XPS Document Writer" until you can obtain a Windows 10 compatible printer driver from your printer manufacturer.


HP Laserjet users can find the "Recommended Solution" for their printer model at http://h20564.www2.hp.com/hpsc/doc/public/display?docId=emr_na-c04675396.

Automated email reminders for customers on Sage Line 50 / Sage Instant

The good thing is that, if you already have Excel, this will cost you nothing. The idea goes like this:

• In the Memo field in the Sage Customer Record, start a dedicated line with something like "#Service date:" and then enter, say, 0630 for the 30th of June each year.
• Run an Excel macro periodically to identify and automatically email customers where the Memo field indicates that the next service is due soon.

SMS messages are possible too, but you may need to pay for each message sent.

Please drop me a note if you are interested in the Excel file that will facilitate this functionality.

Import into Sage from Excel to create a multi-line sales invoice in the Invoicing module

The standard response from most is that if you want to create an invoice in the Invoicing module, then the built in import routines do not allow it, so you'd need a 3rd party import program. You can import the invoice as a transaction to the Audit Trail using the Audit Trail Excel import template though.

However, there is a way to create an invoice in the Invoicing module using standard Sage routines - just not using the Excel import templates supplied by Sage. Sage help files outline the process whereby sales invoices can be created in either Sage Line 50 or Sage Instant here. Whilst this process requires Sage 50 Accounts Professional to raise a Purchase Order, instead, we can create a "virtual" Sage 50 Accounts Professional Purchase Order using only Excel.

A summary of the process is given below. In the summary, Company B is the company wishing to create multi-line invoices in the Invoicing module.

• Company A (or someone in Company B) creates a virtual Purchase Order using Excel and sends it using Transaction email to Company B. Company B receives this as a Sales Order.
• The Sales Order details are matched and updated.
• Then Transaction Email automatically creates a Sales Invoice in Sage 50 Accounts / Sage Instant.
• Company B sends this Sales Invoice to Company A using Transaction Email. Company A receives this as a Purchase Invoice.

The folks at Onion Reporting Software have a free Excel template to create the "virtual" Purchase Order needed for this process.

Run-time error '1004': Unable to set the Visible property of the PivotItem class

There are two circumstances in which this error will arise when issuing a [PivotItem].Visible = True command in Visual Basic:

1. The sort order of the PivotField containing the PivotItem is set to anything other than xlManual; and,
2. The ShowAllItems value of the PivotField containing the PivotItem is set to False and an attempt is made to set a PivotItem containing no data (i.e. [PivotItem].RecordCount = 0) to True.

To process [PivotItem].Visible = True safely you should issue the following commands first:

[PivotField].AutoSort xlManual, [PivotField].SourceName
[PivotField].ShowAllItems = True

Sage Line 50 / Instant month end reporting using Onion Reporting Software's Onion product

One of the issues users frequently ask about is how they can monitor any “back postings” made in Sage since their previous monthly reporting cycle.  For example, a business may have a reporting cycle that produces reports for a month end five working days after that month end.  Using Onion as the reporting option will produce month by month spend figures for each month in the year to date as at working day five of the new month.  If, on working day six of the new month, a late posting is made to the previous month, how is the user of the Onion reporting pack next month to be made aware of the prior month posting?

The [Onion info] sheet of each Onion workbook records the “Last transaction number” at the time of workbook creation.  This identifies the cut-off point for the reporting pack.  The next time an Onion workbook is created, the user can ask to have postings made after that cut-off point identified in the new Onion workbook. The user ticks a check box, provides the last transaction number from the previous reporting pack, and runs the report. On completion the user can set the NEW_POST filter on the [TB Pivot (YTD)] sheet to Y to see what months have been posted to since the last report. Double clicks on the month totals will show the transaction detail of what was posted.

Excel VBA range find method not working

Just beat my head off a brick wall for the best part of a day on this!

The Find method failed to locate the reference of a text value in a row because I had hidden the row. I was using LookIn:=xlValues. When I changed it to LookIn:=xlFormulas, it worked as expected. Who knew!

P.S. Excel 2003 - don't know about other versions.

Adding Debits and Credits in Excel without a helper column

Irritatingly, I keep coming across exports from accounting systems where the figures are always stated as absolute amounts with a separate column showing Dr or Cr to indicate the signage, Dr being + and Cr being -.

It isn't always convenient to add a helper column but a formula in the form of 
=SUMIF($A$1:$A$10,"Dr",$B$1:$B$10) -SUMIF($A$1:$A$10,"Cr",$B$1:$B$10) will subtract the sum of all the Cr values in column B from the sum of the Dr values in column B based on the Dr/Cr designations in column A.

I've noted that the range for SUMIF isn't necessarily a single column array. So, if you had Dr/Cr designators in columns A and C and amounts in columns B and D the formula could have the form =SUMIF($A$1:$C$10,"Dr",$B$1:$D$10) -SUMIF($A$1:$C$10,"Cr",$B$1:$D$10).

Sage Aged Debtor and Aged Creditor reporting in Excel

See the YouTube video showing Onion Reporting Software's comprehensive Aged Debtor and Aged Creditor reports produced from Sage Instant or Sage Line 50 accounting data by their Onion software.

Dynamic print range(s) in Excel

I have a spreadsheet where I always want to print 2 columns of interest on the same page. The thing is those columns are different columns for nearly every print run. I can write a formula which calculates which columns they'll be. So, I set about writing a formula for the Sheet1!Print_Area.

Sheet1!Print_Area =offset($A$1,0,MonthNo+2,counta($A:$A),2) 

If MonthNo is 1 Columns C and D will print for as many rows as there are entries in column A.

If MonthNo is 2 Columns D and E will print for as many rows as there are entries in column A. 

And so on...

Using calculations for Print_Area provides the basis for some interesting possibilities:

If I identify a need to print everything on one page in certain circumstances, but on two pages (that need to be carefully controlled) in other circumstances, I can set up a number of calculated names and "cascade" these to create a sophisticated multi-range print range.

e.g.
Sheet1!OnePage =offset($A$1,0,3,counta($A:$A),MonthNo)

Sheet1!Pg1 =offset($A$1,0,3,counta($A:$A),6)

Sheet1!Pg2 =offset($A$1,0,6+3,counta($A:$A)+1,MonthNo)

Sheet1!TwoPages =Sheet1!Pg1,Sheet1!Pg2

Sheet1!Print_Area =if(MonthNo>6,Sheet1!TwoPages,Sheet1!OnePage)

A word of warning: if the areas that make up TwoPages fit within a simple rectangular area, with no cells within that rectangle unselected, Excel will replace the calculation for Print_Area with the absolute cell references of the upper left and lower right cells when MonthNo > 6. I've even seen Excel crash in these circumstances. However, to avoid this possibility, I find it convenient to include an extra row in one of the ranges. (That is why the calculated number of rows for Pg2 is counta($A:$A)+1, one more than the counta($A:$A) for Pg1) 

DSN-less ODBC connection for Sage

Sometimes you would prefer to have a DSN-less ODBC connection to your Sage data.

In Excel 2003, if you execute the following vba snippet, you'll get a DSN-less connection to your Sage demonstration company and a QueryTable containing the company name of the data at that location.

ActiveSheet.QueryTables.Add( _
    Connection:="ODBC;" & _
        "Driver={Sage Line 50 v18};" & _
        "DIR=C:\ProgramData\Sage\Accounts\2012\DemoData\accdata", _
    Destination:=Range("A1"), _
    Sql:="select Name from Company").Refresh

You should edit the snippet where it is highlighted in yellow to reflect your Sage version (e.g. Sage 2010 = v16, Sage 2011 = v17, Sage 2012 = v18, Sage 2013 = v19, etc ...) 

Once the QueryTable is created, you can edit the datapath in the Connection Dialog to access other company data and edit the query to access other tables.

Happy browsing!

Excel #VALUE! error in simple formula

I've come across the dreaded "space in a cell" scenario quite a few times in client Excel spreadsheets.  Some seem to think that [space]+[enter] is the same as [delete]. The trouble is that if you try to perform any arithmetic using the cell reference alone you'll get a #VALUE! error.
With the formula =A2+B2-C2 you'll get the error if you put non-numeric text in any of the three cells.

There are several different strategies I employ in these types of scenario. 

One observation is that the Sum() function will treat a space (or any text for that matter) as zero. So, instead of A2+B2-C2 you can write the formula as Sum(A2)+Sum(B2)-Sum(C2) or Sum(A2,B2)-Sum(C2). Note that you cannot write Sum(A2,B2,-C2) or a space character in cell C2 will provoke a #VALUE! error - it tries to calculate the negative of a space character before the Sum function has had a chance to turn it into a zero for arithmetic purposes. An element of error proofing is thus applied to your spreadsheets if you always wrap individual cell references with Sum().

However, you'll often come across this where it may be very time consuming to change an existing spreadsheet to employ this strategy.  In these cases I find that the Edit | Goto (F5 key) followed by Special... and then selecting the Constants radio button in combination with only the Text tick box identifies all cells with text in them.  The ones that look empty are the ones with spaces in them.  Delete the spaces and things should start working again.

Last, but not least, if there ought not to be any valid space characters in the highlighted cells, I would use search and replace.  A single space character goes in the "Find what:" field and the "Replace with:" field is left empty. 

I hope this helps.

Replacement function for GETPIVOTDATA

Why is GETPIVOTDATA so cumbersome to work with!  I'm always having to do stuff like:
=if(iserror(GETPIVOTDATA([parameter set])),0,GETPIVOTDATA([parameter set]))

It gets to be a real pain as I have to edit both [parameter set]s to have it work properly and there's always the possibility of making a mistake that is not immediately apparent.

I finally decided I'd create my own function that returned 0 instead of erroring:

Function GETMYPIVOTDATA(data_field, pivot_table, _
    Optional field1, Optional item1, _
    Optional field2, Optional item2, _
    Optional field3, Optional item3, _
    Optional field4, Optional item4, _
    Optional field5, Optional item5, _
    Optional field6, Optional item6, _
    Optional field7, Optional item7, _
    Optional field8, Optional item8, _
    Optional field9, Optional item9, _
    Optional field10, Optional item10, _
    Optional field11, Optional item11, _
    Optional field12, Optional item12, _
    Optional field13, Optional item13, _
    Optional field14, Optional item14)
Dim retval
Err.Clear
On Error Resume Next
retval = pivot_table.pivottable.GetPivotData(data_field, _
    field1, item1, field2, item2, field3, item3, field4, item4, _
    field5, item5, field6, item6, field7, item7, field8, item8, _
    field9, item9, field10, item10, field11, item11, field12, item12, _
    field13, item13, field14, item14 _
    )
On Error GoTo 0
GETMYPIVOTDATA = IIf(IsError(retval), 0, retval)
End Function

Now I just use =GETMYPIVOTDATA([parameter set])

More than two tables in an outer join query

I've often come across situations where MS Query complains that you can't have more than two tables involved in an outer join query. I'm not sure if this occurs with every ODBC driver but it certainly does with the Microsoft Text Driver (*.txt; *.csv) driver.  I've always worked around this by using multiple queries to get the job done but I recently discovered that it seems to be a MS Query issue rather than a restriction associated with the text driver. I stumbled across this because Excel 2007 and later allow you to edit the SQL associated with your data connections without invoking MS Query.  I had a three table query with inner joins and discovered that one of the inner joins needed to be an outer join.  I made the change to the SQL manually and it worked.  MS Query still complains and will refuse to deal with the query so I often adopt a hybrid approach to dealing with such queries as follows:

• construct the query in MS Query using inner joins only;
• manually change the inner join details in the SQL generated by MS Query to outer joins

A simple example follows using three data files (Data, Data2 and Data3) each with an ID column on which they are joined. The initial SQL generated from MS Query was:

SELECT Data.ID, Data.DName, Data2.D2Name, Data3.D3Name
FROM Data.txt Data, Data2.txt Data2, Data3.txt Data3
WHERE Data.ID = Data2.ID AND Data.ID = Data3.ID

After editing the SQL became:

SELECT Data.ID, Data.DName, Data2.D2Name, Data3.D3Name
FROM (Data.txt Data
left outer join Data2.txt Data2 on Data.ID = Data2.ID)
left outer join Data3.txt Data3 on Data.ID = Data3.ID

The output of this query looks as follows:

ID	DName	D2Name	D3Name
1	Unrestricted		Unrestricted
2	Restricted	Restricted
3	Designated		Designated

The outer join operation is evidenced by the null values returned under D2Name and D3Name.


For Excel 2003 I make the changes to the SQL by opening an Immediate window in the Visual Basic Editor and typing something like [?activecell.querytable.commandtext] followed by [Enter]. This reports the first SQL above on rows two to four of the window. Then I edit the first line to read [activecell.querytable.commandtext = "SELECT Data.ID, Data.DName, Data2.D2Name, Data3.D3Name FROM (Data.txt Data left outer join Data2.txt Data2 on Data.ID = Data2.ID) left outer join Data3.txt Data3 on Data.ID = Data3.ID"] followed by [Enter]. This changes the SQL without the need to use MS Query.  A simple refresh of the data will show the multitable query with the outer joins in place.

I hope you will find this helpful.  It has always worked for me, but I worry that there must be some good reason that MS Query complains about such a query.

Your comments would be very welcome

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