getting unorganised data analysed

[sanyo1234]

My data looks like

[TABLE="width: 384"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]Branch name[/TD]
[TD]AA[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]|[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]|[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Branch name[/TD]
[TD]AB[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]|[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]|[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]|[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]|[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Branch name[/TD]
[TD]AC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]|[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]


The branch name and one of the totals fall in the same column and same difference between the rows is NOT maintained

The output should be like

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]AA[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]4[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]


Please help me i am struggling with this since there are nearly 200 branch names
Thank you

 

[hiker95]

sanyo1234,

Here is a macro solution for you to consider, that is based on your two flat text displays, that uses two arrays in memory, and, will adjust to the number of raw data rows, and, columns.

You can change the worksheet names in the macro, and, I assume that both worksheets already exist.

With your raw data in Sheet1, range A1:E15, your results will be written to Sheet2, range A1:E3.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorganizeData()
' hiker95, 09/24/2017, ME1023097
Application.ScreenUpdating = False
Dim w1 As Worksheet, w2 As Worksheet
Dim a As Variant, i As Long, c As Long, lr As Long, lc As Long, bn As Long
Dim o As Variant, j As Long
Set w1 = Sheets("Sheet1")   '<-- you can change the sheet name here
Set w2 = Sheets("Sheet2")   '<-- you can change the sheet name here
With w1
  lr = .Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
  lc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc)).Value
  bn = Application.CountIf(.Columns(1), "Total")
  ReDim o(1 To bn, 1 To lc)
End With
For i = 1 To UBound(a, 1)
  If a(i, 1) = "Branch name" Then
    j = j + 1: o(j, 1) = a(i, 2)
  ElseIf a(i, 1) = "Total" Then
    For c = 2 To UBound(a, 2)
      o(j, c) = a(i, c)
    Next c
  End If
Next i
With w2
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .UsedRange.Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorganizeData macro.

 

[sanyo1234]

sir the debugger stopped at Line 21

o(j, c) = a(i, c)

line please rectify it

 

[Rick Rothstein]

Here is another macro for you to try...

[table="width: 500"]
[tr]
	[td]Sub BranchTotals()
  Dim Rw As Long, Ar As Range, Source As Worksheet, Result As Worksheet
  Set Source = Sheets("Sheet3")
  Set Result = Sheets("Sheet4")
  For Each Ar In Source.Columns("A").SpecialCells(xlConstants).Areas
    Rw = Rw + 1
    Result.Cells(Rw, "A") = Ar(1).Offset(, 1).Value
    Result.Cells(Rw, "B").Resize(, 4).Value = Ar(Ar.Count).Offset(, 1).Resize(, 4).Value
  Next
End Sub[/td]
[/tr]
[/table]

 

[hiker95]

sir the debugger stopped at Line 21

o(j, c) = a(i, c)

line please rectify it

sanyo1234,

In order to continue I would like to see your actual workbook/worksheets, with the raw data in one worksheet, and, the results (manually formatted by you) in the other worksheet.

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com