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

[WilliamCTX]

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

[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.

[chukleswk · Accepted solution]

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"

[binuacs · Accepted solution]

@WilliamCTX one way of doing this

[WilliamCTX]

@chukleswk Oops, my bad. I missed including the source data. Thanks for the insight!

[WilliamCTX]

@binuacs 

Interesting approach with the use of macros.

I'll explore it further.

Thanks for sharing!