How to pull data into other columns when row #'s change between reports

**larryl19567** · 06-27-2019, 03:53 PM

book1.JPG
book2.JPG

Kind of lost on writing this macro, as I just began a few weeks ago. book1 and book2 images attached, and also workbook also attached to explain the below:

Sheet1 has customer information, including customer name, #, and all the related documents (invoice/payment info). The Customer ID and Customer Name are only at the heading of the information, and I need it to be pulled into each row to be included with the documents (invoice/payment info). However, the number of documents can change on a weekly basis, therefore, changing what rows all the data is on.

Basically, I need Sheet1 to end up looking like Sheet1(2). As previously stated, the number of rows per customer can change weekly, etc.

Is this even possible?

**Olly** · 06-28-2019, 05:49 AM

You can use Power Query to normalise the data from your source file:

let
    Source = Excel.Workbook(File.Contents("C:\temp\vbahelp.xlsx"), null, true),
    WorkSheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(WorkSheet, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each not Text.Contains([Type], "Total")),
    #"Added Reference" = Table.AddColumn(#"Filtered Rows", "Reference", each try Number.FromText([Ref Nbr]) otherwise null, type number),
    #"Added Company Number" = Table.AddColumn(#"Added Reference", "Company Number", each if [Reference] = null then [Type] else null, type text),
    #"Filled Down Company Number" = Table.FillDown(#"Added Company Number",{"Company Number"}),
    #"Added Company Name" = Table.AddColumn(#"Filled Down Company Number", "Company Name", each if [Reference] = null then [Ref Nbr] else null, type text),
    #"Filled Down Company Name" = Table.FillDown(#"Added Company Name",{"Company Name"}),
    #"Filtered null Reference" = Table.SelectRows(#"Filled Down Company Name", each [Reference] <> null and [Reference] <> ""),
    #"Choose Columns" = Table.SelectColumns(#"Filtered null Reference",List.Combine({{"Company Number", "Company Name"},Table.ColumnNames(#"Promoted Headers")})),
    #"Changed Type" = Table.TransformColumnTypes(#"Choose Columns",{{"Type", type text}, {"Terms", type text}, {"Ref Nbr", Int64.Type}, {"Doc Date", type date}, {"Current", Currency.Type}, {"1 To 30", Currency.Type}, {"31 To 60", Currency.Type}, {"61 To 90", Currency.Type}, {"Over 90", Currency.Type}, {"Total", Currency.Type}})
in
    #"Changed Type"

Change filepath / name and worksheet name to suit.

**jindon** · 06-28-2019, 06:19 AM

VBA

Sub test()
    Dim x, i As Long
    x = Filter([transpose(if((c1:c50000="Customer Total")+(c1:c50000="Company:"),row(1:50000)))], False, 0)
    For i = 0 To UBound(x) - 1
        With Cells(x(i) + 2, "a").Resize(x(i + 1) - 2 - x(i))
            .Value = Cells(x(i) + 1, "d")
            .Columns(2).Value = Cells(x(i) + 1, 3)
        End With
        Cells(x(i) + 2, "m").Resize(, 2).Value = Cells(x(i) + 1, "g").Resize(, 2).Value
    Next
End Sub

**larryl19567** · 07-02-2019, 08:07 AM

Thanks Jindon, that works like a beauty. I do have one final thing I'm struggling with on this macro, and it will be completed. I need macro to calculate the Percentage of Total lines:

sheet4.JPG

Each "Percentage of Total" line needs to calculate the percentage of the Grand Total. For example, row 4 (columns G-L), needs to calculate =G3/G33, or =H3/H33, and so forth. However, as previously stated, the company totals, percentage of totals and grand totals' row numbers will change every time I run my report. The final "Percentage of Total" line located on row 34, is just calculating the percentage of the total (i.e. =G33/L33, or =H33/L33, etc.)

I have already used conditional formatting macro to format the percentage of total rows to 0.00% format.

Attached is excel workbook as well (see Sheet4)

**jindon** · 07-02-2019, 09:26 AM

Try

Sub test()
    Dim x, i As Long, r As Range
    Application.ScreenUpdating = False
    With Sheets("sheet1")
        If .[a2] <> "Company:" Then Exit Sub
        .Columns("a:b").Insert
        With .[a1:b1]
            .Value = Array("Customer Name", "Customer ID")
            .Font.Bold = True
        End With
        x = Filter([transpose(if((c1:c50000="Customer Total")+(c1:c50000="Company:"),row(1:50000)))], False, 0)
        For i = 0 To UBound(x) - 1
            With Cells(x(i) + 2, "a").Resize(x(i + 1) - 2 - x(i))
                .Value = Cells(x(i) + 1, "d")
                .Columns(2).Value = Cells(x(i) + 1, 3)
            End With
            Cells(x(i) + 2, "m").Resize(, 2).Value = Cells(x(i) + 1, "g").Resize(, 2).Value
        Next
        .Rows("2:3").Delete
        With .Cells(1).CurrentRegion.Offset(1).Columns(1).SpecialCells(2)
            For Each r In .Areas
                With r(r.Count + 1).Resize(2, 12)
                    .ClearContents
                    .Range("a1:a2").Value = _
                    Application.Transpose(Array(r(1).Value & " Total", "Percentage of Total"))
                    .Rows(1).Range("g1:l1").Formula = _
                    "=subtotal(9," & r.Offset(, 6).Address(0, 0) & ")"
                    .Font.Bold = True
                End With
            Next
        End With
        With .Range("a" & Rows.Count).End(xlUp)(2).Resize(4)
            .EntireRow.ClearContents
            With .Rows("3:4")
                .Font.Bold = True
                .Range("a1:a2").Value = Application.Transpose(Array("Grand Total", "Percentage of Total"))
                .Rows(1).Range("g1:l1").FormulaR1C1 = "=subtotal(9,r2c:r[-5]c)"
            End With
        End With
        .Columns.AutoFit
    End With
    Application.ScreenUpdating = True
End Sub