Forum Discussion
Multiple If formulas with conditions
I am in need a formula that will look at multiple conditions for the data I have. My sample data is below.
If the value in A is the same, and the value in B is the same, then join the values in C and put them in D. The formula I have for that function works (=IF(A2=A3, IF(A3=A4, TEXTJOIN(",",TRUE,C2,C3,C4),TEXTJOIN(",",TRUE,C2,C3)),"").
What I need the complete formula to do is this:
If the value in A is the same, and the value in B is the same, then join the values in C and put them in D, but if the value in A does not match, then copy the value from C2 to D2.
I hope I explained that well enough. I'm thinking it needs to be a nested formula, but I cannot figure it out. Any help will be greatly appreciated.
This task is best handled by GROUPBY but that function is not yet available in general release of 365.
For now I offer a garden variety Reduce -
=LET( header, {"ID", "Company", "Plan"}, uniqueID, SORT(UNIQUE(ID)), Organize, LAMBDA(acc, v, LET( details, HSTACK(Company, Plan), record, FILTER(details, ID = v), comp, TAKE(record, 1, 1), joined, ARRAYTOTEXT(TAKE(record, , -1)), VSTACK(acc, HSTACK(v, comp, joined)) ) ), REDUCE(header, uniqueID, Organize) )*For the future*:
=LET( row_fields, HSTACK(ID, Company), GROUPBY(row_fields, Plan, ARRAYTOTEXT, , 0) )That formula really has star quality! Both the HSTACK and ARRAYTOTEXT nail it.
.
- I've never used VBA or macro before. Once I copy this into the developer, what do I do?
Both replies by Patrick2788 are cell formulas, not VBA. The first one will work in Excel in "regular" Microsoft 365, the second one is currently (early May 2024) only available to Microsoft 365 Insiders.
An alternative which works in legacy Excel 2013 could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
- I've never used power query before, so I'm not sure I understand exactly what you did here.
