Split Data Based on Rows Values/Formatting

[wcrateli]

I don't know if this is even remotely possible without touching VBA or if it is if there is a simple way of coding what I'm trying to accomplish.

In my field I deal with aging accounts for multiple homeowners - around 300 units to be exact. When I pull reports from the software that detail every posting by account, it shows up like this (assume the hyphens represent the separation between two columns):

Unit Number 1
Post 1 - $100
Post 2 - $50
Post 3 - $90
Subtotal - $240

Unit Number 2
Post 1 - $100
Post 2 - $90
Subtotal - $190

and so on...

Based on this example, you can see that there is no uniformity in the number of charges (number of rows) so I cannot simply use the "split data" tool to achieve this. I need to be able to group these sets of data based on the subtotal amount being greater than a given number. Basically I would be weeding out accounts less than $200, for instance. Any advice would be greatly appreciated.

 

[ParamRay]

Try a procedure like this to normalize the data:

Sub Normalize()

  Dim ws1 As Worksheet
  Dim ws2 As Worksheet
  Dim j As Long
  Dim A As Range
  
  Set ws1 = ActiveSheet
  Set ws2 = Worksheets.Add
  
  ws2.Range("A1:B1").Value = Array("Unit", "Total")
  j = 2
  
  For Each A In ws1.Columns("A").SpecialCells(xlCellTypeConstants).Areas
    ws2.Range("A" & j).Value = A.Cells(1).Value
    ws2.Range("B" & j).Value = A.Cells(A.Cells.Count).Offset(, 1).Value
    j = j + 1
  Next A
  
  ws2.Columns("A:B").AutoFit
  
  Set ws1 = Nothing
  Set ws2 = Nothing

End Sub