Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

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

@achow1565 

No need for code. You can use WRAPCOLS.

arr = A1:A34

=WRAPCOLS(arr,2)

 

@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
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
best response confirmed by VI_Migration (Silver Contributor)
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. 

@achow1565 

Screenshot_2023-07-07-13-43-38-031_cn.uujian.browser.jpg

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?
http://e.anyoupin.cn/EData/?s=email
put here online tools rather than Excel.
1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
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. 

View solution in original post