Multiple If formulas with conditions

[Danielle1505]

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. 

[#2]

@Danielle1505 

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

[#3]

@Danielle1505 

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.

[#4]

@Patrick2788 

*For the future*:

=LET(
    row_fields, HSTACK(ID, Company),
    GROUPBY(row_fields, Plan, ARRAYTOTEXT, , 0)
)

[#5]

I've never used VBA or macro before. Once I copy this into the developer, what do I do?

[#6]

I've never used power query before, so I'm not sure I understand exactly what you did here.

[#7]

@Danielle1505 

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.

[#8]

Oh. It looks like VBA coding. I will try it.

[#9]

@Patrick2788 

That formula really has star quality!  Both the HSTACK and ARRAYTOTEXT nail it.  .

[#10]

@Danielle1505 

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.

[#11]

@Patrick2788 

I tried plugging in the formulas suggested, and they didn't work. Maybe it's just me.

[#12]

@Danielle1505 

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.