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 ...
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.
.
