Extract the amount based on the location containing 'total' from different columns

Hi everyone,

Im seeking advice to write the code to handle this kind of scenario.

The objective is to extract the total amount. However, due to the report layout, the 'total' column is not consistently each block (separated by salesperson). Please refer to the screenshot named "sample" (blue highlighted cells contain the needed information) and the desired outcome.

FYI, there are more than 100 salespeople in the actual report, so any ideas on how to scan through every column and extract the amount would be greatly appreciated.

Thanks

Expected Outcome.png

Sample.png

Sample.xlsx

Workflow

Datasets

Preparation

Expression

Accepted answers

chuckleswk

1st - Please attach an example input dataset so it's easier to help build an example workflow.

2nd - You can use the following workflow to get the desired results.

Bring the data in
Use a cleanse to remove null rows and columns
I then set a flag so I know what records I want to keep. This is done by finding the records that have "Name:" in the row and set that flag as well as any follow-on ones as 1
Filter down to just the ones you want to keep
I then re-use that flag to group the individuals results together and then I add a row-order for each group so that I can keep those rows in the right order
Use a transpose on all of the records with the "Keep Flag" and the "Row_order" as a Key column so that I can use those to join back later
Filter out the records that contain "Total" as I know those are the only rows I want to keep
Join the transposed data to those columns that contain "Total"
Join the Data back to the original Data (in which I have selected just the first column, "Keep Flag", and "Row_order") on the "Keep Flag" and the "Row_Order"

Extract Amount.yxmd

Extract Amount.xlsx

binu_acs

@WilliamCTX one way of doing this

PEriod_Total_25022025.yxzp

All comments

OTrieger

@WilliamCTX
I would put a RecordID and then aFfilter and look for Period, connect another RecordID name it Category and then union the data together and sort it out based on the RecordID. With Multi Row Formula tool copy the Category to all the bellow cells.

Now you put it into a Batch Macro based on Category, so each time one sales person is been pulled into the macro. With Dynamic Rename making the first row into the header, and then you can add a selected tool that removes the unneeded fields.

chuckleswk

1st - Please attach an example input dataset so it's easier to help build an example workflow.

2nd - You can use the following workflow to get the desired results.

Bring the data in
Use a cleanse to remove null rows and columns
I then set a flag so I know what records I want to keep. This is done by finding the records that have "Name:" in the row and set that flag as well as any follow-on ones as 1
Filter down to just the ones you want to keep
I then re-use that flag to group the individuals results together and then I add a row-order for each group so that I can keep those rows in the right order
Use a transpose on all of the records with the "Keep Flag" and the "Row_order" as a Key column so that I can use those to join back later
Filter out the records that contain "Total" as I know those are the only rows I want to keep
Join the transposed data to those columns that contain "Total"
Join the Data back to the original Data (in which I have selected just the first column, "Keep Flag", and "Row_order") on the "Keep Flag" and the "Row_Order"