Forum Discussion
Is this IF statement possible?
I'm working in financials where we contact our borrowers once a year to ask for their financial reports, net worth statements, etc. We do a big mail merge for the clients with 1 or 2 loans, but with bigger clients with 3+ loans, we send those individually so we are extra careful that we have their requests right. I want to automate some of the decision-making around who goes it which pile- "mail merge" or "major borrower".
Rules to see if it's a major borrower:
1) if a "name" (personal or corporate) is on more than 3 loans, their loan goes into the major borrower pile. The name can be
a) a borrower,
b) a financial contact, or
c) a guarantor.
You can only have one borrower per loan, but a loan can have multiple financial contacts or guarantors. I know how to group my financial contacts and guarantors into the same respective cell (ie. Financial contacts being comma delimited, and Guarantors being comma delimeted), so ultimately I can get one row per loan. Can an If statement scan the contents of a cell and look for duplicates across other rows?
Thanks!
bonjourposte Create a concatenated field for Borrowers, Financial Contacts, and Guarantors: If your data is structured such that Borrower, Financial Contacts, and Guarantors are in separate columns, you could create a new column that combines all of these into a single one for each row, making it easier to look for duplicate names.
CombinedNames = [Borrower] & "," & [FinancialContacts] & "," & [Guarantors]
If you want to break the concatenated column into separate names and count their occurrences, you would need to either use Power Query to split the names into rows or use a more complex DAX formula. A simple way is to count occurrences of a name within the combined column.
Count the number of loans per name: Create a measure or calculated column to count how many times each name appears across all rows (loans).
LoanCountPerName = CALCULATE(
COUNTROWS(Loans),
FILTER(
Loans,
CONTAINSSTRING([CombinedNames], [Name])
)
)Create another calculated column to flag whether a borrower, financial contact, or guarantor is a major borrower (more than 3 loans).
IsMajorBorrower = IF([LoanCountPerName] > 3, 1, 0)
Finally, you can use this flag to sort your data into two piles (Mail Merge or Major Borrower).
2 Replies
bonjourposte Create a concatenated field for Borrowers, Financial Contacts, and Guarantors: If your data is structured such that Borrower, Financial Contacts, and Guarantors are in separate columns, you could create a new column that combines all of these into a single one for each row, making it easier to look for duplicate names.
CombinedNames = [Borrower] & "," & [FinancialContacts] & "," & [Guarantors]
If you want to break the concatenated column into separate names and count their occurrences, you would need to either use Power Query to split the names into rows or use a more complex DAX formula. A simple way is to count occurrences of a name within the combined column.
Count the number of loans per name: Create a measure or calculated column to count how many times each name appears across all rows (loans).
LoanCountPerName = CALCULATE(
COUNTROWS(Loans),
FILTER(
Loans,
CONTAINSSTRING([CombinedNames], [Name])
)
)Create another calculated column to flag whether a borrower, financial contact, or guarantor is a major borrower (more than 3 loans).
IsMajorBorrower = IF([LoanCountPerName] > 3, 1, 0)
Finally, you can use this flag to sort your data into two piles (Mail Merge or Major Borrower).
Awesome, thanks, I'll give it a try.
