Creating table from email body and repeating columns

I want to convert an email body from a website form to an easy to read table so we can enter it into our Access database. This is what would come in after copy/pasting the email body into Excel.

Data from email body

I found a https://www.excelhow.net/how-to-transpose-every-n-rows-of-data-into-muliptle-columns-in-excel.html online that would transpose every n rows into columns, so I can get the data to look like this.

After transposing with VBA code

The thing is that my last 4 columns (quantity, customer PN, MN part #, MN serial #s) will repeat depending on how many part numbers there are. I want my final table to look like below. I found this https://community.fabric.microsoft.com/t5/Desktop/How-do-I-stack-several-columns-in-a-table-two-at-a-time-example/td-p/1372872 that is similar to what my goal is, but I couldn't follow along and don't know if it can account for the varying number of repeating last 4 columns. Can somebody help me out with the code?

What I want my final results to be

This will do it. I hope you have 365!

=LET(
    header, INDEX(list, SEQUENCE(, 13, 1, 2)),
    organized, DROP(
        REDUCE(
            "",
            header,
            LAMBDA(a, v,
                LET(
                    r, FILTER(ROW(list), list = v) + 1,
                    IFERROR(HSTACK(a, INDEX(list, r)), "")
                )
            )
        ),
        ,
        1
    ),
    VSTACK(header, organized)
)

I created a function to do this. You'll find it all in the workbook attached.

REDUCE 7.6.xlsx15 KB

Forum Discussion

Creating table from email body and repeating columns

10 Replies

Resources