Multi Row Formula

[Rosaile]

I have a set of data shown below, regarding payments.  The 2 scenarios outlined come from one combined set of data.  My issue is I'm trying to create a column that identifies if a unique ID has installments greater than 1.  (Essentially, New Column would be a Y or N).

The issue I'm having is that I can also have 2 lines for One unique ID because the payor will make a payment to 2 separate payee's (Scenario 2).  So, using the Summarize tool and Group by Unique ID and Count on Installment # does not work because scenario 2 will also show count of more than 1.  

Does anyone have any ideas?  I have also tried playing with the multi-row formula tool, but am not having much luck.

Scenario 1
	Unique ID	Payor	Payee	Gross Bill Amount	Installment Amount	Installment #		New Column (What I Want)
	1	John	Jane	100	25	1		Yes
	1	John	Jane	100	25	2		Yes
	1	John	Jane	100	25	3		Yes
	1	John	Jane	100	25	4		Yes
Scenario 2
	Unique ID	Payor	Payee	Gross Bill Amount	Installment Amount	Installment #		New Column (What I Want)
	2	Bob	Jane	25	25	1		No
	2	Bob	Mary	25	25	1		No

[cmcclellan · Accepted solution]

I think you've basically got the answer anyway - I would use Summarize, maybe a filter for Installment # as well, then join that back on to the original data set to get the New Column calculation.

[Rosaile]

I had the unique ID's wrong in scenario 1 and have updated, would that change your response?

My issue with summarize is if I count installments then items like scenario 2 will count higher than 1, but they are false positive for multiple installments.

For filters, I guess I can do an installment greater than 1, and then create a column on everything true to say "Yes".  What would I do though for the installments labeled #1 that would be in the false part of that filter, since those lines also need to say "Yes"

Thanks!

[Rosaile]

I got it figured out. 

I did a formula and created a column to say "Yes" on installments > 1 and "Maybe" for installments = 1.  Then I filtered on Maybe's.  Summarized on the true side of the filter and grouped by / counted the unique id's.  Anything that had more than 1 unique id were the false positives for multiple installments.  i then rejoined and unioned my data from the filter and added a new formula to update the Yes or No Field for multiple installments. 

[cmcclellan]

Awesome :)