Example Transformation and formatting

Hi All - I have question around transforming / formatting an incredibly ugly Excel file (export from PDF). I have essentially rows 1-3 as important parts of a clothing product (color / product name / style number) listed on 3 rows repeating (Total file is roughly 900 rows). I then need to apply the corresponding headers / values to those items. Desired output is in yellow below as it may best describe what I am trying to do. Sample Excel file is also attached. Any ideas would be greatly appreciated as this is beyond anything I can think of. Thanks!

@SGenzer

Find more posts tagged with

AI Studio

Excel

Getting Started

ETL + Data Prep

Accepted answers

kayman

Try this example process

Community_excel.rmp

All comments

kayman

Try this example process

Community_excel.rmp

miked

Kayman - Thanks so much this is fantastic. Can I just ask what is the "rec" in the Set Macro? I don't believe I've seen that before. It looks like you are using mod 3 to end the process once lines 1-3 have been processed? Any explanation would be helpful but not necessary. Thank you!

kayman

That's just a random name I gave to the macro, short for record. You can name it any way you want.
I'm indeed using mod 3 but rather than ending it will just increase this rec value, so the first 3 lines will be 1, the next 3 lines will be 2, the next 3 lines will be 3 and so on till the end. So each record (set of 3 lines) will have a unique value now.

This way you get like an ID per record (set of 3 lines) which you can than iterate through using the loop values operator.