Label Unique values containing at least one negative.

[fordfold]

Hi

Am trying to write a workflow that identifies where we have a matching credit and debit in a spreadsheet.

It has to pick out where the values in three columns match and then Invoice amount adds up to zero (so one line -100 the other would be 100). But there may also be a third line of 100 so it will call out all three and then the user can check manually.

Have used the Summarize tool to turn turning the negatives to positive then but that also calls out rows that have no negative.

So either need a way of only selecting groups of rows that contain a negative or a better way all together.

Hope that makes sense, as always i'm sure there's a simple way of doing it but is escaping me!

Date	Amount	Cost Centre	Account	NEW Amount	Current RESULT	RESULT NEEDED
24/10/2018	28.00	SZ400	ASD400FB	28	Yes	NO
24/10/2018	28.00	SZ400	ASD400FB	28	Yes	NO
15/07/2018	-2033.62	SZ400	ASD400FB	2033.62	Yes	Yes
15/07/2018	2033.62	SZ400	ASD400FB	2033.62	Yes	Yes
15/07/2018	2033.62	SZ400	ASD400FB	2033.62	Yes	Yes
15/07/2018	2075.25	SZ400	ASD400FB	2075.25	Yes	Yes
15/07/2018	-2075.25	SZ400	ASD400FB	2075.25	Yes	Yes
02/10/2018	28.88	SZ400	ASD400FB	30	No	No
02/10/2018	1411.12	A00444	ASD400FB	40	No	No
02/10/2018	28.88	SZ400	ASD500SZ	40	No	No
02/10/2018	1411.12	A00444	ASD500SZ	40	No	No

[BrandonB · Accepted solution]

What about something like this? Use a filter tool to split the amounts into positive and negative streams, then use a formula on the negatives to turn them positive, then use a join tool because the amounts can now be matched up along any other fields that you want to consider. 

 

[fordfold]

Thanks, that's the missing piece I was looking for.

Used the filter and join as you suggested to pull out the non-contra rows. 

Then put in another join from the original dataset and the join to pull out just the contra rows.

This mean I am not losing any data.