Combining multiple rows for 1 record

Question

I'm struggling with this one and I'm hoping someone has a better approach.

In the attached example, I have a report that has 3 separate rows for every record.

I need several fields off of each of those rows, and I need to output them as a single row into Excel.

The only key identifier for each record is Reference Number, and it only exists in the first row of the record.

I typically do this using the multi-row function. That works fine for just a few fields, but in this case I'm going to have to do it with a LOT of fields, so all of those multi-row functions will start to accumulate on my workflow. I'm wondering if there isn't a simpler, more efficient way of accomplishing the same thing.

Any help is appreciated.

multirow example.pdf

RogerS · Answer

You can use the attach macro and set the records per group to 3.  This will create the grouping field you need out of the D anchor.

Groupsof.yxmc

Phermes1 · Answer

Oooooh - I like this idea! And that sounds pretty easy to do too! Thanks!

Luke_C · Answer

Hi @Phermes1

I would suggest using a multi-row tool to assign each group of rows an Record ID and a Row ID (so for the first one, 1 for all 3 rows as the record ID and 1, 2, and 3 as the Row IDs). Then you'd likely need to parse each row separately, it appears to be a fixed width file. Once the rows are all parsed you can join together based on the record ID.