Help required with Netting off positives and Negatives and only giving remaining records

[raghusrealm]

I have the raw data as in TABLE 1 with positives and negatives in Quantity.

I need the end result as shown in TABEL 2

 

[messi007]

@raghusrealm,

Could you share a sample data without screen shot.

Why you have two rows for Bananas in EU and not for Apple.

Best regards,

[Qiu]

@messi007 
here  is the OCRed Data I prepared. but still figuring the logic here.

[binuacs]

@raghusrealm This can be achieved with the help of the multi-row formula tool.  First you need to group the data based on the positive and negative value then do the cross tab (take the sum of the quantity, this will make the negative and positive quantity to 0) , then transpose and filter out quantities with 0.

@Qiu thank you for providing the sample data :)

[danilang]

Hi @binuacs 

Very clever solution here!  The only issue is that It's dependent on the order of the entries.  With differing input orders you get different solutions

Luckily the fix is simple.  Before the Multi-row tool, sort on Location, Fruits and Quantity.

Dan   

[binuacs]

@danilang Thanks you for the suggestion, I updated the workflow and attached!!

[raghusrealm]

@binuacs 

Thanks a lot for building the logic. But for some reason, I am getting a download failure error. Would you be able to share it again please?

Regards,

Raghu

[binuacs · Accepted solution]

@raghusrealm Attaching the workflow