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 |
