Add information from parts of a subtotal row to data rows above it

[tavmeister]

I am new to Alteryx and learning the ropes. I've created basic workflows but have a situation that I don't know how to handle. I have an excel file that contains text information in subtotal rows that I need to add to data rows that appear before that subtotal row in the excel file. I'm struggling to figure out how to get information from a row BELOW one or more data rows, and add it to each data row. Another potential complication is that the excel file is a report file, and so it has a header row with a report title and page numbers, as well as a heading row (that repeats at the top of each page), that I do not want to extract into my results.

Source fileResults

I have coloured the fields that make up a row the same colours. The Yellow highlighted cells in the Results screenshot are items from the subtotal row that appears below the related data rows. The Subtotal row is a single row (or to be more precise, it is a single cell), but it has 2 items I need to extract from it as 2 separate fields (salesorder and PONumber). As an explanation, the data rows 9-11 (cells Bxx to Oxx) in the Source File need to have the 2 items from cell B13 (the Total salesorder: and PO Number: items) added before each of their columns in the Results.

Row 22 is the last line on Page 1 (which is a data row), and row 23 is the first line of Page 2 (the page 2 report header line). The Subtotal for data row 22 is row 29. In between these 2 rows is a report header line and a report title line, neither of which I want to extract into my results. This is an example of how the data breaks across 2 pages of the source file/report.

I've uploaded a sample of the source and results sheets.

Can someone please help?

thanks!

[DavidP]

Here's how I would do it

[DavidP · Accepted solution]

I forgot to filter out the row that originally contained the salesorder and PO Number from the final result - it's the row with the nulls. Just add a filter at the left input of the join tool or at the final output.

[tavmeister]

Thanks so much David, this is perfect! I acknowledged your second reply as the solution as it is the final step that completes your workflow (I added the filter at the end of the workflow, and it works like a charm). I really appreciate your quick and accurate solution!