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!

 

[Fabulist]

Link: Example

Result: Imgur: The most awesome images on the Internet

It 'works' but eats up some values, thanks again.

 

[sheetspread]

I compared the result in your picture to the standard tab, what's missing?

 

[Fabulist]

I think I became blind for a second there, I did not even notice the fact some lines appear as doubles, I actually though they were completely missing. So it seems it does work and it is accurate, the only problem is that it separates accounts and duplicates them in order to make a proper separation.

I cannot thank you enough for this, this is very helpful, I will attempt to learn from the macro you created!

 

[Rick Rothstein]

I think I became blind for a second there, I did not even notice the fact some lines appear as doubles, I actually though they were completely missing. So it seems it does work and it is accurate, the only problem is that it separates accounts and duplicates them in order to make a proper separation.

I cannot thank you enough for this, this is very helpful, I will attempt to learn from the macro you created!

Can you post a copy of the spreadsheet that results when you first get your data so we can see what the software suite produces originally? That way we don't have to try and type it from the picture that you posted in order to test code against it.

 

[Fabulist]

Can you post a copy of the spreadsheet that results when you first get your data so we can see what the software suite produces originally? That way we don't have to try and type it from the picture that you posted in order to test code against it.

It is on post #11, at the "Link: Example", it contains a sample of the problematic area. This area you will see is repeated over and over for hundreds of times, and while separated from each other, titles come into place again and again, like the company's name and address. The latter is not included in the example, but it is a similar line like the "General Trial Balance" one, which is also repeated.

 

[Rick Rothstein]

It is on post #11, at the "Link: Example"

I saw that, but the worksheet you show there looks nothing like the picture of the output from the software suite you showed in the link you provided in Message #1... I wanted to see the worksheet that resulted when you loaded the output from the software suite in its raw form (that is, before any macro code touches it).

 

[Fabulist]

The raw form looks very similar to what I have in the "Example.xlsx", it just get repeated over and over. As for the link in message one, it is a direct picture from the "Example" xls file, nothing is changed. If you are talking about "how it should look" or "standard look" it also looks the same to me with what I have in the "Example.xlsx".

Note that "Example.xlsx" containts two sheets, "Hell on Earth" and "Standard", which are the software's raw output and an example of how it should look from me, respectively.

 

[sheetspread]

the only problem is that it separates accounts and duplicates them in order to make a proper separation.

Try this one:

Sub formattba2c()
Dim x%, y As Variant, lr%
y = Array(1, 4, 11, 12, 15, 16, 20, 21, 25, 26, 29, 30, 36, 37, 40)
With ActiveSheet.UsedRange
.Value = .Value
.UnMerge
Columns(30).Copy
Columns(29).PasteSpecial SkipBlanks:=True
Columns(30).ClearContents
Range("a16").Resize(.Rows.Count - 17, 4).Cut Destination:=Range("a14")
With Range("a14").Resize(.Rows.Count - 19, 4)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
Range("a14").Copy
.PasteSpecial Paste:=xlPasteFormats
End With
For x = 42 To 1 Step -1
If IsNumeric(Application.Match(x, y, 0)) = False Then
Columns(x).Delete
End If
Next
lr = Cells(Rows.Count, 1).End(xlUp).Row
For x = lr - 1 To 14 Step -1
If Cells(x, 1) = Cells(x - 1, 1) And Application.CountA(Cells(x, 3).Resize(, 13)) <> 0 Then
Cells(x, 3).Resize(, 13).Copy
Cells(x - 4, 4).PasteSpecial SkipBlanks:=True
Rows(x).Delete
Else
End If
If Application.CountA(Cells(x, 3).Resize(, 13)) = 0 Then
Rows(x).Delete
End If
Next
.Columns.AutoFit
.Rows.AutoFit
End With
End Sub

 

[Fabulist]

sheetspread I very much appreciate your effort, this a semi-translated duplicate of the original file, with the full data: TRIAL BALANCE 30-09-2015

Your macro works perfectly on the "Example" file, but for some reason it does not function well on a full scale. The first thing I noticed is that the codes on the left appear erroneously; e.g. code "12" does not appear at all and its values are not aligned. "12-00" appears as "36861" and other values below are misaligned or misplaced, while others appear correctly.

I have translated everything beside the descriptions, which should not be a problem. Unfortunately, I lack the skills to both edit and understand your macro, so I cannot correct this myself, excuse me for this.

Thanks again.

PS: I will keep all links alive for future reference, in case someone else encounters a similar problem in the future - in case they are offline and someone is looking for the original files, please contact me.

 

[Rick Rothstein]

The raw form looks very similar to what I have in the "Example.xlsx", it just get repeated over and over. As for the link in message one, it is a direct picture from the "Example" xls file, nothing is changed.

Nothing is changed? I'm confused. The link in Message #1 shows the Account Numbers in a set of merged cells, the Description in another set of merged cells, the Debit and Credit values in the Record, Record Balance and Year columns are stacked one on top of the other with the Debit in a single cell and the Credit in a merged cell under it... and those are just what I see off the top of my head... with the exception of the header row, your example file shows all the data neatly in their own non-merged rows and columns. So, they sure look a lot different to me, hence, my confusion. In order to transform one into the other, I need a copy of the worksheet containing the original data as it first appears when brought into Excel in, as you described it in Message #1, the "completely problematic and difficult to edit form".