How to insert row(s) after certain set of data

[Punter007]

Hi,

I have around 2-3K of line items and I would like to insert new rows after certain lines.

So, in my file I have data in column F - "Reference" and column G "Account type" . Taking both as my parameter i will have to insert a row in the next line.

For example, in first row, I have "TA908244/AB1" as reference and account type as "AP" , the second row has "TA908244/AB1" but the account type is "GL". So, here I will consider both the rows as one data and insert a new row in the third row. In this example there is only one "GL" row but other cases for one reference value there can be multiple "GL" rows

Overall the logic is that after each "Reference value" I will need to insert a row.

I have attached sample file for reference. Tab 1 is the raw data and tab 2 has the format in which I want the output.
Sample.xlsx
Please let me know if there is anyway to do this. Thanks in advance.

[davesexcel]

You can insert above 34 in column J

Sub InsertAbove34()
    Dim ws As Worksheet
    Dim cnt As Long
    Dim x

    Set ws = ActiveSheet

    With ws
        cnt = .Cells(.Rows.Count, "J").End(xlUp).Row
        For x = cnt To 3 Step -1
            If .Cells(x, "J") = 34 Then Rows(x).Insert
        Next
    End With



End Sub

[Punter007]

Thanks very much this was really helpful.

The code has accomplished almost what I needed, except in reference value containing "/AB2" should also be moved to different row like other data rows. In the attached image I have highlighted in red Attachment 823159

Also the last line , I see one row data alone hangs separate, in the same image I have highlighted in Yellow. If there is something that be done then it would be helpful. If not then that's fine

[sandy666]

maybe something like this with Power Query

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TCN = List.RemoveLastN(Table.ColumnNames(Source),1),
    DateLoc1 = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-US"),
    DateLoc2 = Table.TransformColumnTypes(DateLoc1, {{"Date2", type date}}, "en-US"),
    IF = Table.AddColumn(DateLoc2, "IF", each if [Typer] = "SM" then 14.5 else [Help]),
    Grp = Table.Group(IF, {"IF"}, {{"All", each _, type table}}, GroupKind.Local),
    BlankRows = Table.TransformColumns(Grp, {{"All", each Table.InsertRows(_, Table.RowCount(_),
        {[
            Date = null,
            Date2 = null,
            Typer = null,
            Text = null,
            ReferenceValue = null,
            AccountType = null,
            Account = null,
            C = null,
            Amount = null,
            Key = null,
            Help = null,
            IF = null
        ]})
    }}),
    TSC = Table.SelectColumns(BlankRows,{"All"}),
    Exp = Table.ExpandTableColumn(TSC, "All",TCN),
    Date = Table.TransformColumnTypes(Exp,{{"Date", type date}, {"Date2", type date}})
in
    Date

[Punter007]

Thanks very much for the help. How do I go about inserting this query in my file? I tried converting the data to table and then inserting via Power Query but didn't work.

[sandy666]

• copy M from the post
• select your range
• use Ctrl+T to change range to table
• add formula as last column (Help)
• Data tab - from table
• Advanced Editor - replace all there with copied M
• name of the table (eg. Table1) must be the same as in M - fix it manually if necessary
• headers of the table must be the same as in M

if you don't know Power Query will be hard if something goes wrong so try to read Power Query documentation first