Possible to rearrange really broken Trial Balance sheets?

[Fabulist]

Hello everyone!

I am using a software suite which produces Trial Balances in a completely problematic and difficult to edit form. It looks like this (partial example, incorrect totals):

View image: TB

Usually, and ideally, a software generated Trial Balance would look like this:

[TABLE="width: 1182"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Description[/TD]
[TD="colspan: 2"]Record[/TD]
[TD="colspan: 2"]Record Balance[/TD]
[TD="colspan: 2"]2014 Y/E[/TD]
[TD="colspan: 2"]Current Balance[/TD]
[TD="colspan: 2"]This Period[/TD]
[TD="colspan: 2"]Year to Date[/TD]
[TD="colspan: 2"]Current Balance[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]-[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[/TR]
[TR]
[TD]12-01-00-0002[/TD]
[TD]Floor-Separators[/TD]
[TD="align: right"]565,77[/TD]
[TD][/TD]
[TD="align: right"]565,77[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]565,77[/TD]
[TD][/TD]
[TD="align: right"]565,77[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12-01-00-0003[/TD]
[TD]Door-Separators[/TD]
[TD="align: right"]1.584,30[/TD]
[TD="align: right"]1.000,00[/TD]
[TD="align: right"]1.584,30[/TD]
[TD="align: right"]600,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.584,30[/TD]
[TD][/TD]
[TD="align: right"]1.584,30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12-01-00-0004[/TD]
[TD]Lead[/TD]
[TD="align: right"]1.454,44[/TD]
[TD][/TD]
[TD="align: right"]1.454,44[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.454,44[/TD]
[TD][/TD]
[TD="align: right"]1.454,44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12-01-00-0005[/TD]
[TD][/TD]
[TD="align: right"]334,69[/TD]
[TD][/TD]
[TD="align: right"]334,69[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]334,69[/TD]
[TD][/TD]
[TD="align: right"]334,69[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12-01-00-0006[/TD]
[TD]Hydralic Installations[/TD]
[TD="align: right"]557,53[/TD]
[TD][/TD]
[TD="align: right"]557,53[/TD]
[TD][/TD]
[TD="align: right"]200,00[/TD]
[TD="align: right"]200,00[/TD]
[TD="align: right"]150,00[/TD]
[TD="align: right"]150,00[/TD]
[TD="align: right"]75,00[/TD]
[TD="align: right"]75,00[/TD]
[TD="align: right"]557,53[/TD]
[TD][/TD]
[TD="align: right"]557,53[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12-01-00-0007[/TD]
[TD]Crystals[/TD]
[TD="align: right"]692,59[/TD]
[TD][/TD]
[TD="align: right"]692,59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]692,59[/TD]
[TD][/TD]
[TD="align: right"]692,59[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12-01-00-0008[/TD]
[TD]WEST POINT[/TD]
[TD="align: right"]704,33[/TD]
[TD][/TD]
[TD="align: right"]704,33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]704,33[/TD]
[TD][/TD]
[TD="align: right"]704,33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12-01-00-0009[/TD]
[TD]Air-condition[/TD]
[TD="align: right"]621,76[/TD]
[TD="align: right"]1.000,00[/TD]
[TD="align: right"]621,76[/TD]
[TD="align: right"]700,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]621,76[/TD]
[TD][/TD]
[TD="align: right"]621,76[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12-01-00-0010[/TD]
[TD]Air-condition[/TD]
[TD="align: right"]484,97[/TD]
[TD][/TD]
[TD="align: right"]484,97[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]484,97[/TD]
[TD][/TD]
[TD="align: right"]484,97[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12-01-00-0011[/TD]
[TD]Monitor R140[/TD]
[TD="align: right"]7.404,18[/TD]
[TD][/TD]
[TD="align: right"]7.404,18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.404,18[/TD]
[TD="align: right"]5.500,00[/TD]
[TD="align: right"]7.404,18[/TD]
[TD="align: right"]1.999,00[/TD]
[/TR]
[TR]
[TD]12-06[/TD]
[TD]Other mechanical equipment[/TD]
[TD="align: right"]2.169,56[/TD]
[TD][/TD]
[TD="align: right"]2.169,56[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.169,56[/TD]
[TD][/TD]
[TD="align: right"]2.169,56[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12-06-00[/TD]
[TD]Other mechanical equipment[/TD]
[TD="align: right"]2.169,56[/TD]
[TD][/TD]
[TD="align: right"]2.169,56[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.169,56[/TD]
[TD][/TD]
[TD="align: right"]2.169,56[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12-06-00-0000[/TD]
[TD]Air-condition[/TD]
[TD="align: right"]909,78[/TD]
[TD][/TD]
[TD="align: right"]909,78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]909,78[/TD]
[TD][/TD]
[TD="align: right"]909,78[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The fact that the cells are separated the way they are makes it incredibly difficult to use the data, a situation enhanced by titles that are on top of each other instead of side to side. In case you have not seen a TB before, the table in the example is separated by the company's name (software - specific) and goes on for many accounts.

Would it be possible that this mess is somehow fixed and rearranged to the ideal format automatically, or at least eliminate the problematic cells / columns so editing and summing is not a frustrating situation with doubtful results?

I have uploaded the file here, since copy pasting it in the forum did not produce the same results: Example in case anyone wants to take a direct look.

Thank you to anyone who replies in advance!

 

[sheetspread]

All I can think of is trying to find other reports in the system to run instead (hopefully less of an Excel formatting disaster than what's above) and perhaps combining them. Ideally one with just the beginning balances and another with transactions.

 

[Fabulist]

All I can think of is trying to find other reports in the system to run instead (hopefully less of an Excel formatting disaster than what's above) and perhaps combining them. Ideally one with just the beginning balances and another with transactions.

Unfortunatelly, the system produces only this type of reports; no PDF, no ASCII etc.

 

[sheetspread]

I meant formatting might be easier with two smaller reports. What else do you have in the system?

 

[Fabulist]

I meant formatting might be easier with two smaller reports. What else do you have in the system?

Unfortunatelly, there is nothing really there for us in the system, the only filetype it can produce is this Excel and the only format is this one.

 

[sheetspread]

You don't have a purchases or transactions report? Or a general ledger summary? Just a "General Trial Balance"?

 

[Fabulist]

You don't have a purchases or transactions report? Or a general ledger summary? Just a "General Trial Balance"?

The "General Trial Balance" I threw in there was an example, I do have every file necessary. However, the Trial Balance is interconnected to a program which automatically transfers the required numbers and makes adjustments as well as calculations automatically. In order for it to function properly, the Trial Balance needs to be in the standard and common form, otherwise it cannot be read. Currently, I manually re-adjust the Trial Balances and have to either redo all links or reformat it to the standard format, something dangerous and time consuming, considering I need to adjust over 100 Trial Balances with the same format. The safest but most time consuming method is to readjust all formulas after removing the broken intermediary cells and columns. The quickest, but not as safe, is to rearrange the Trial Balance to the standard format.

There is a specific software, old and crippled, that many companies use, and it produces Trial Balances this way - I cannot avoid it or make it produce it another way.

 

[sheetspread]

Yes, the newer database programs are better if you have them, otherwise you're limited to these awful excel unfriendly reports.

A macro did refine your sample above, but I don't know how well it will handle different reports and exceptions:

Sub formattb()
Dim x%
With ActiveSheet.UsedRange
.Value = .Value
.UnMerge
.Columns.AutoFit
Rows.AutoFit
For x = .Rows.Count To 1 Step -1
If Application.CountA(Cells(x, 1).Resize(, .Columns.Count)) = 0 Then
Rows(x).Delete
End If
Next
For x = .Columns.Count To 1 Step -1
If Application.CountA(Cells(1, x).Resize(Rows.Count)) = 0 Then
Columns(x).Delete
End If
Next
Range("a9").Resize(.Rows.Count - 10, 2).Cut Destination:=Range("a8")
With Range("a8").Resize(.Rows.Count - 9, 2)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
Range("a8").Copy
.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End With
For x = .Rows.Count To 8 Step -1
If Application.CountA(Cells(x, 5).Resize(, 9)) = 0 Then
Rows(x).Delete
End If
Next
End With
End Sub

 

[Fabulist]

Excuse my delay, my reply for some reason was not posted.

Thank you for taking your time writting this macro, I appreciate it. It seems to partially work; it does clean up the problematic columns but some numbers also dissapear.

I posted a link with the problematic TB, but I do not see it in my original post, was it deleted due to forum rules or did I completely forget about it - not sure. I can repost it if you wish to see and reproduce the problem, but whether I select the whole sheet or only cells with numerical values, some data is always lost.

 

[sheetspread]

'General Trial Balance

Account movements from 01/01/2015 up to 30/09/2015

with the rest just as you have above is what was in the link (might have been removed accidentally, not against the forum rules unless the hosting site was questionable)

the macro didn't miss any numbers with that sample. If you can post how yours looked after running it and another report that I can try it on.