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.
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.
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) )- I've never used VBA or macro before. Once I copy this into the developer, what do I do?
No vba is used. The only thing you may need to account for in the formulas are the named items:
If your data has these defined names, you can plug the formula in and calculate.
PeterBartholomew1 - hats off to the Excel team for PIVOTBY/GROUPBY! I really like how these formulas read with the ETA. The only change I'd make is having the [filter array] argument appear after the 'function' argument.
*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.
.
