May 03 2024 10:44 AM
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.
May 03 2024 01:03 PM
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)
)
May 03 2024 01:44 PM
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.
May 03 2024 02:13 PM
*For the future*:
=LET(
row_fields, HSTACK(ID, Company),
GROUPBY(row_fields, Plan, ARRAYTOTEXT, , 0)
)
May 03 2024 02:34 PM
May 03 2024 02:57 PM
May 03 2024 02:58 PM
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.
May 03 2024 03:04 PM
May 03 2024 03:13 PM
That formula really has star quality! Both the HSTACK and ARRAYTOTEXT nail it. .
May 03 2024 04:04 PM - edited May 03 2024 04:08 PM
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.
@Peter Bartholomew - 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.
May 07 2024 08:22 AM
May 08 2024 09:32 AM
Which formula didn't work? The second uses GROUBY which is only available on the insider's beta channel at the moment. Otherwise REDUCE 5.3 from above should work with non-insider channels.