How to implement a for loop for this data?

[mehappyyou]

Let's say I have a mock data that looks like this:

I want to pair a row with RECEIVE AND DELIVER for each order.

For example:

in Order 1, we will have 2 pairs (ID 2 and 4) and (ID 1 and 3). 

in Order 2, we will have 1 pair (ID 1 and 3).

In Order 3, we have no pair because both are "DELIVER".

and then filter them so those rows that have a group will be in TRUE.

I am unable to find a way to do this... Any help would be greatly appreciated.

[Prometheus]

@mehappyyou You can use the Summarize tool to group by ORDER and Count Distinct the values in RECEIVE_OR_DELIVER. After that, you can use a Filter tool to pass counts that are > 1 and then join them back to the original data on ORDER to get only those that have both DELIVER and RECEIVE in the RECEIVE_OR_DELIVER column.

 

 

 

[mehappyyou]

Hi Prometeus, 

Thanks very much for your help! 

Your logic works perfectly for my current data but if I were to add a 9th row for Order 3 for example:

In the image above, I'd only want to have Record #7 and #9 as they're a pair with Deliver and Receive in Order 3. Record #8 does not have another row with the "RECEIVE" to pair up with so it should not appear in there. Do you know how I can fix this?

[Prometheus · Accepted solution]

@mehappyyou If you want the pairs to be side-by-side, you can use this. If you want them to be in the same format as before, you can take the output and split it into two Select tools -- one with the left side of the data and one with the right side of the data and Union them. The 9th row will be left out as it exits the Join at the R anchor. 

[apathetichell]

Follow @Prometheus 's suggestion - but instead of unioning - join on order number. The resulting data set will show the universe of potential deliever/receive pairs in your dataset.

[mehappyyou]

Thank you so much!!! I came here expecting to not get any response but you went beyond and even created the workflow for me. Really appreciated!