Organize data Idea

[Ladyj07]

Hello,



Thank you.

[JohnTopley]

I would use VBA but as you have 365, then perhaps one of the 365-formula wizards can provide a formula-based solution.

See attached

Option Explicit

Sub demo()
Dim a, b, col
Dim i As Long, j As Long, n As Long, lr As Long
Dim EmpName As String, EmpId As String, accrual As String
col = Array(1, 3, 4, 5, 6, 7)

With Sheets("Sheet1")

    lr = .Cells(Rows.Count, "C").End(xlUp).Row
    a = .Range("A1:G" & lr)
    ReDim b(1 To UBound(a, 1), 1 To 10)
    
    For i = 1 To UBound(a, 1)
        EmpName = Trim(Split(a(i, 1), ":")(1))
        EmpName = Trim(Replace(EmpName, "Number", ""))
        EmpId = Trim(Split(a(i, 1), ":")(2))
        i = i + 1
        accrual = Trim(Split(a(i, 1), ":")(1))
        i = i + 1
        Do
          If IsDate(a(i, 1)) Then
            n = n + 1
            b(n, 1) = EmpName: b(n, 2) = EmpId: b(n, 3) = accrual
            For j = 0 To 5
              b(n, j + 4) = a(i, col(j))
              If b(n, j + 4) = "----" Then b(n, j + 4) = 0
              If b(n, j + 4) = "" Then b(n, j + 4) = 0
            Next j           
          End If
          i = i + 1
        Loop Until a(i, 1) = "Total for Accrual:"
          b(n, 10) = a(i, 7)
          n = n + 1
          If i >= UBound(a, 1) Then Exit For
    Next i
End With

With Sheets("Sheet2")
     n = n - 1
    .[A2].Resize(10000, 19).Clear
    .[A2].Resize(n, 10) = b
    .Columns(5).Resize(, 6).NumberFormat = "#0.00"
    .Columns(1).Resize(, 9).AutoFit
    
End With
End Sub

See Sheet2 (RUN button)

UPDATE: I see you have formula from Windknife in next post!

[Ladyj07]

@JohnTopley Ohh it didn't cross my mind that this could be done via macros. I will run it against my original file and let you know.

[Ladyj07]

John, In my original file I have :



Is there a way the code be modify so It will look for anything that start with "Totals for Bank " as after this depending on the row it could change to "ops" or Corp"

Right now this line is: "Totals " and I understand you put it like that because when I upload the test file I changed it manually to said that but the original titles are as shown above.


Thank you

[JohnTopley]

See attached

[Ladyj07]

THANK YOU!! You are a live saver. Everything works perfectly and now I can audit this so easily. Thanks!!

[JohnTopley]

Glad to have helped and many thanks for the rep

[windknife]

One way,
T1

=LET(
  a,A1:G1000,
  b,VSTACK(TOCOL(IF(LEFT(INDEX(a,,1),8)="Employee",ROW(a),1/0),3),1000),
  IFERROR(DROP(REDUCE("",SEQUENCE(ROWS(b)-1),LAMBDA(m,n,VSTACK(m,LET(c,FILTER(a,(SEQUENCE(ROWS(a))>=INDEX(b,n,1))*(SEQUENCE(ROWS(a))<INDEX(b,n+1,1)),""),d,TRIM(TEXTBEFORE(TEXTAFTER(INDEX(c,1,1),"Employee:"),"Number:")),e,TEXTAFTER(INDEX(c,1,1),"Number:"),f,TEXTAFTER(INDEX(c,2,1),"Accrual : "),VSTACK(REDUCE(HSTACK("employee name","employee id","accrual bank","date posted","Accrued","Expired","Taken","Adjustments","Period Net","Balance"),SEQUENCE(ROWS(c)-4,,5),LAMBDA(x,y,IF(INDEX(c,y,1)="",x,IF(INDEX(c,y,1)="Total for Accrual:",VSTACK(x,HSTACK(d,e,f,"-","-","-","-","-","-",INDEX(c,y,7))),VSTACK(x,HSTACK(d,e,f,INDEX(c,y,1),TAKE(INDEX(c,y,),,-5),"-")))))),"",""))))),1),"")
)