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]

Oh I apologize for this, English is not my native language so I am having hard time understanding what I failed to explain properly.

#1 has pictures and copy pasted data from the "Example" file in #11, which has two sheets:

1. Hell on Earth has a sample of the problematic Trial Balance (you can now find the full Trial Balance in #19 instead of a sample). I translated it accordingly so anyone can preview it directly, so descriptions will differ in the full Trial Balance in #19.

2. Standard is my interpretation of how Hell on Earth should look like ideally (and how most modern Trial Balances look).

So the original file is on #19 names "TRIAL BALANCE 30-09-2015", should be made to look similar to what "Standard" looks like in the "Example" file located on #11. Ignore "Hell on Earth" in the "Example" file.

 

[Rick Rothstein]

Oh I apologize for this, English is not my native language so I am having hard time understanding what I failed to explain properly.

#1 has pictures and copy pasted data from the "Example" file in #11, which has two sheets:

1. Hell on Earth has a sample of the problematic Trial Balance (you can now find the full Trial Balance in #19 instead of a sample). I translated it accordingly so anyone can preview it directly, so descriptions will differ in the full Trial Balance in #19.

2. Standard is my interpretation of how Hell on Earth should look like ideally (and how most modern Trial Balances look).

So the original file is on #19 names "TRIAL BALANCE 30-09-2015", should be made to look similar to what "Standard" looks like in the "Example" file located on #11. Ignore "Hell on Earth" in the "Example" file.

Some question (all for the "Example" file)....

When I look at the "Example" file, I see many "General Trial Balance" tables, one under the other, all the way down the sheet. Is the code supposed to do something with all of them? If so, what, show them one under the other or put them on separate sheets?

What about the company information shown in merged cell A1 for the first table... should that go anywhere?

What about the date shown in merged cell AG1 for the first table... should that go anywhere?

What about the "Movement" date range shown under the words "General Trial Balance" in merged cell H5 for the first table... should that go anywhere?

What about the "Transferred" information shown on Rows 112 and 114 for the first table... should that go anywhere?

 

[Fabulist]

Some question (all for the "Example" file)....

When I look at the "Example" file, I see many "General Trial Balance" tables, one under the other, all the way down the sheet. Is the code supposed to do something with all of them? If so, what, show them one under the other or put them on separate sheets?

What about the company information shown in merged cell A1 for the first table... should that go anywhere?

What about the date shown in merged cell AG1 for the first table... should that go anywhere?

What about the "Movement" date range shown under the words "General Trial Balance" in merged cell H5 for the first table... should that go anywhere?

What about the "Transferred" information shown on Rows 112 and 114 for the first table... should that go anywhere?

1. You see it repeated because they did not bother to adjust the software so it consolidates / combines them, so we see the titles over and over again. The codes in accounting represent the location of the specific transaction, they are necessary to exist, but not the word "Code" itself.

2. No, the company information is unnecessary, it is just repeated.

3. AG1 is also information which is repeated and unnecessary.

4. Transferred totals and totals in general are unnecessary.

This is an example of a trial balance which looks very decent: http://www.azurecurve.co.uk/images/...e_Summary/Realign_Trial_Balance_Summary_9.png

And this is how ours should look like, exluding the titles & totals, which are not needed.

 

[Rick Rothstein]

1. You see it repeated because they did not bother to adjust the software so it consolidates / combines them, so we see the titles over and over again. The codes in accounting represent the location of the specific transaction, they are necessary to exist, but not the word "Code" itself.

2. No, the company information is unnecessary, it is just repeated.

3. AG1 is also information which is repeated and unnecessary.

4. Transferred totals and totals in general are unnecessary.

This is an example of a trial balance which looks very decent: http://www.azurecurve.co.uk/images/...e_Summary/Realign_Trial_Balance_Summary_9.png

And this is how ours should look like, exluding the titles & totals, which are not needed.

Wow, that original data sheet is definitely "Hell On Earth"... I have another question about it. Skipping the first row of data for now (I'll explain why in a minute), the second data record starts on Row 21 and every 7th row after that; however, the Debit amount for it starts on Row 19, two rows before the data item it's for! And that two row offset continues down to the bottom of the data in the table. Okay, now back to the first record... the Code and Description for it starts on Row 14 (and that is fine as it is in line with the 7-row offset progression of the other records; however, instead of having its Debit value located two rows offset from the record's Code/Description, the offset is only one row for it. What I need to know is... are those offsets (one row for the first record, two rows thereafter) correct.... is that how the data is really laid out or was that just something that crept into your sample data when you created it?

 

[Fabulist]

Wow, that original data sheet is definitely "Hell On Earth"... I have another question about it. Skipping the first row of data for now (I'll explain why in a minute), the second data record starts on Row 21 and every 7th row after that; however, the Debit amount for it starts on Row 19, two rows before the data item it's for! And that two row offset continues down to the bottom of the data in the table. Okay, now back to the first record... the Code and Description for it starts on Row 14 (and that is fine as it is in line with the 7-row offset progression of the other records; however, instead of having its Debit value located two rows offset from the record's Code/Description, the offset is only one row for it. What I need to know is... are those offsets (one row for the first record, two rows thereafter) correct.... is that how the data is really laid out or was that just something that crept into your sample data when you created it?

Haha yes, a colleague wrote this and I simply left it since I agree with him, it is Hell on Earth indeed.

The trial balance you have is 100% accurate and direct via the software, it stands for a specific firm, the only thing changed is that I translated certain fields. However, the offset method will not work unless you scan the differences in some way, trial balances vary, if the macro works in this trial balance and no other that could have more or less lines, more or less codes, etcetera, this means it will work only into this specific trial balance, which in other words does not matter since every other problematic trial balance will look very similar to this one but will have more or less codes / lines. I am saying this because I believe you are targeting specific cells / rows by counting them, but to be honest I do not understand your thoughts on this entirely.

 

[Rick Rothstein]

...but to be honest I do not understand your thoughts on this entirely.

Look at the first record for your Example... Code 12 is in cell A14, its Description is in cell B14, but its Debit value is in cell K13 (I would have expected it to be in cell K14)... the offset between the Code and Debit is one row. Now look as the second record... Code 12-100 is in cell A21, its Description is in cell B21, but its Debit value is in cell K19 (I would have expected it to be in cell K21)... the offset between the Code and Debit is now two rows.

 

[sheetspread]

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 added some lines to remove repeating headers and took out the value conversion that misread shorter account #s as dates, it seems to work except on the first line (where the pattern might break) so that's the only manual adjustment required.

Sub formattb3()
Dim x%, y As Variant, lr%
y = Array(1, 4, 11, 12, 15, 16, 20, 21, 25, 26, 29, 30, 36, 37, 40)
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
Rows(.Rows.Count - 37).Resize(37).Delete
For x = .Rows.Count To 21 Step -1
If Cells(x - 11, 7).Value = "General Trial Balance" Then
Cells(x - 20, 7).Resize(22).EntireRow.Delete
End If
Next
.UnMerge
Columns(30).Copy
Columns(29).PasteSpecial SkipBlanks:=True
Columns(30).ClearContents
Range("a14").Resize(.Rows.Count - 18, 4).Cut Destination:=Range("a12")
With Range("a12").Resize(.Rows.Count - 16, 4)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Range("a35").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
For x = .Rows.Count 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
Application.ScreenUpdating = True
End Sub

It's not as good as one of Rick's codes, but see how it runs

 

[Fabulist]

I added some lines to remove repeating headers and took out the value conversion that misread shorter account #s as dates, it seems to work except on the first line (where the pattern might break) so that's the only manual adjustment required.

Sub formattb3()
Dim x%, y As Variant, lr%
y = Array(1, 4, 11, 12, 15, 16, 20, 21, 25, 26, 29, 30, 36, 37, 40)
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
Rows(.Rows.Count - 37).Resize(37).Delete
For x = .Rows.Count To 21 Step -1
If Cells(x - 11, 7).Value = "General Trial Balance" Then
Cells(x - 20, 7).Resize(22).EntireRow.Delete
End If
Next
.UnMerge
Columns(30).Copy
Columns(29).PasteSpecial SkipBlanks:=True
Columns(30).ClearContents
Range("a14").Resize(.Rows.Count - 18, 4).Cut Destination:=Range("a12")
With Range("a12").Resize(.Rows.Count - 16, 4)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Range("a35").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
For x = .Rows.Count 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
Application.ScreenUpdating = True
End Sub

It's not as good as one of Rick's codes, but see how it runs

Complex and outstanding, from a first look, it seems like it works and it is in fact accurate (exluding the first code). I cannot thank you enough for this, fingers crossed in hope that it will work in other similar trial balances as well - if not, I will update here with more information. The only problem was that; when I highlighted the entire sheet, the total was different from the total of the original file. However, some accounts that I sampled were accurate. Time will tell, I will need time to test this one and confirm if something is wrong.

Can you tell me what in particular I should manually adjust so I eliminate undesired effects into this trial balance and others? What adjustments should I make in case another trial balance, similar to this one, has more / less codes and numbers?

Look at the first record for your Example... Code 12 is in cell A14, its Description is in cell B14, but its Debit value is in cell K13 (I would have expected it to be in cell K14)... the offset between the Code and Debit is one row. Now look as the second record... Code 12-100 is in cell A21, its Description is in cell B21, but its Debit value is in cell K19 (I would have expected it to be in cell K21)... the offset between the Code and Debit is now two rows.

You are right, and the problem is that although K14 looks highlighted, K13 is reported for me. As for A21 and B21 I believe it is a similar issue like with K14 / K13 cells, but I do not see cell B21 at all. In reality, when I have Excel to navigate me to B21, it forwards me to A21. As I said, hell on earth indeed.

You should check sheetspread's macro, it looks like he did it - well for this trial balance at least. I will need to check it with others as well, but I do not have access to do so right now unfortunatelly.

 

[sheetspread]

the total was different from the total of the original file

If you sum each column and compare to the totals below the unaltered report they do match, but maybe there's more to it

Can you tell me what in particular I should manually adjust so I eliminate undesired effects into this trial balance and others? What adjustments should I make in case another trial balance, similar to this one, has more / less codes and numbers?

Just drag those two 259568.69 down (first account) and delete the row. I think that's a break in the pattern, as Rick said:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
7											Record				Balance Record
8
9	Code			Description							Debit				Debit
10
11											Credit				Credit
12
13										4	366,574.89				107,006.20
14	12			???/??-????.?G???.-???? ???/??S ??????S						3
15										2
16										1	259,568.69
17
18
471										5	28,326.71				2,814.95
472										4
473	14			?????? ??? ?????S ??????S??S						3
474										2
475										1	25,511.76
476
477
478											5,818.23				5,818.23
Sheet 1 (12)

there are 2 rows in between instead of 3. That wasn't a problem on the smaller sample because the debit box was nonblank, but I'm not sure how other reports look.

 

[Fabulist]

Thank you for your time and effort people, I will report back with results / updates if necessary.