Please help with formatting and summarising data

HW1

I have the output from a pdf parsing and a bit of filtering from a workflow as:

RecordID	Value	Match
18	: Issue Date: 27/02/2021:	-1
54	TRADE WASTE FOR THE PERIOD ENDING: 27/02/2021 ——_—_———————	-1
64	*** 1.5 METRE BIN	0
65	02/02/21 1 BIN 37.50 3.75 41.25	-1
66	16/02/21 1 BIN 37.50 3.75 41.25	-1
67	*** 1.5 METRE BIN RECYCLING	0
68	03/02/21 1 BIN 22.50 2.25 24.75	-1
69	17/02/21 1 BIN 22.50 2.25 24.75	-1
74	INVOICE NUMBER: 140073852102	0
82	. Issue Date: 27/02/2021	-1
83	Random & Sons Pty Ltd	0

The column [Value] can be further split by the space delimiter using the Text to Columns tool but the identification is where I can use help.

I want the output as 

Co./Last Name	Date	Supplier Invoice No.	Amount
Random & Sons Pty Ltd	27/02/2021	140073852102	$75
Random & Sons Pty Ltd	27/02/2021	140073852102	$45

The problem I am having is that the [Amount] column is a sum of the previous two columns i.e. $75 = sum of col [Record ID] 65 & 66 i.e. 37.5 + 37.5 and likewise $45 = sum from [Record ID] 68 & 69

How can I get the needed output? I know it can be done using the formula tool or multi row formula however I can use some help here.

Ben_H

Hi @HW1,

I've put together an example workflow for you that does get you to the output you need.

It isn't intended to be a full solution though, rather it's supposed to give you ideas on how you might want to tackle a more robust solution.

I've used a few methods (that may not necessarily be the best when you try to apply it to a wider dataset) that should be useful.

Regards,

Ben

HW1 Example.yxmd