Creating table from email body and repeating columns

Copper Contributor

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 bodyData 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 codeAfter 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 beWhat I want my final results to be

10 Replies


No need for code. You can use WRAPCOLS.

arr = A1:A34



@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?

Do you have a sample you can share?

@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
Buyers Name
testy mctesty
Buyers email
email address removed for privacy reasons
United States
Product Line
Parts Description
Shipping Tracking Numbers(s)
Customer Return Order #
Reason for Returning Parts
Overhaul / Repair
Customer Part Number
MN Part #
MN Serial #s
126; 128
Customer Part Number
MN Part #
MN Serial #s
best response confirmed by VI_Migration (Silver Contributor)


This will do it. I hope you have 365!

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

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



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;
This worked! I will need to get on 365 but it worked fine on the online 365 version!
Glad it worked!
where would I put this code in Excel?
put here online tools rather than Excel.