Creating table from email body and repeating columns

[achow1565]

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 link 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 link 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

[#2]

@achow1565 

No need for code. You can use WRAPCOLS.

arr = A1:A34

=WRAPCOLS(arr,2)

[#3]

@Patrick2788 that is much simpler!

The second part I'm trying to do is get A27:A34 (the even cells) under the columns created for A19:26. The headers are the same so I don't want to be creating more columns, rather more rows. Do you know of a solution for that?

[#4]

Do you have a sample you can share?

[#5]

@Patrick2788 this would be the copy/paste from the email body. Every other row is a header. You'll see the headers repeat for quantity, customer PN, MN PN, and MN SN. The headers only repeat once in this example, but the form can allow it to repeat as many times as entries are present.

Company Name

test

Buyers Name

testy mctesty

Buyers email

email address removed for privacy reasons

Country

United States

Product Line

Mechanical

Parts Description

rod

Shipping Tracking Numbers(s)

fedexnumberhere

Customer Return Order #

C123456

Reason for Returning Parts

Overhaul / Repair

Quantity

2

Customer Part Number

123456

MN Part #

A1001

MN Serial #s

126; 128

Quantity

1

Customer Part Number

234567

MN Part #

A1002

MN Serial #s

226

[#6 · Accepted solution]

@achow1565 

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. 

[#7]

@achow1565 

 

create temp table aa as 
select udf_fillna_m(iif(F_A like 'Company Name' ,rowid,''),'x')  grp,udf_fillna_m(iif(F_A like 'Quantity' ,rowid,''),'y')  grp2,iif(lag(F_A) over() like 'Company Name',F_A,'') f01,iif(lag(F_A) over() like 'Buyers Name',F_A,'') f02,iif(lag(F_A) over() like 'Buyers email',F_A,'') f03,iif(lag(F_A) over() like 'Country',F_A,'') f032,iif(lag(F_A) over() like 'Quantity',F_A,'') f04,iif(lag(F_A) over() like 'MN Part #',F_A,'') f05,iif(lag(F_A) over() like 'MN Serial #s',F_A,'') f06 from Creating_table_from_email;
create temp table bb as 
select  grp,grp2,group_concat(f01,'') f01,group_concat(f02,'') f02,group_concat(f03,'') f03,group_concat(f032,'') f032,group_concat(f04,'')
||'</td><td>'||group_concat(f05,'')||'</td><td>'||group_concat(f06,'') f06 from aa group by grp,grp2;
//select * from bb;
select * from ((select grp,f01,f02,f03,f032 from bb where grp2='') join (select grp,f06 from bb where grp2!='') using(grp));
//select * from Creating_table_from_email;

[#8]

This worked! I will need to get on 365 but it worked fine on the online 365 version!

[#9]

Glad it worked!

[#10]

where would I put this code in Excel?

[#11]

http://e.anyoupin.cn/EData/?s=email
put here online tools rather than Excel.