Forum Discussion
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. ...
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.
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 |
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.
- This worked! I will need to get on 365 but it worked fine on the online 365 version!
- Glad it worked!
