Formulas to concatenate multiple rows from report into first row - Xlookup?

[dconnors]

I am using Excel MS 365 v 2202 and am trying to find the most straightforward formulas to add to a report exported from Salesforce that will concatenate cells from multiple rows if certain criteria is met. It has 1-X number of rows per client and I only want the information to show on the first row the client name shows up. This will be used by others so I need it to be fairly easy for them to manage (ie run the report, paste it, ensure the formulas go to end of data, remove all rows that are blank in both column D and E).

The first three columns of the screen print contain information from the report, it will always be sorted with the Names in order and with the Current column sorted with 1 first, then 0.

The yellow two columns are the results I want to see. I was hoping there was an easy formula using the new =Xlookup.

I looked on this YouTube channel and didn't see how I could apply any of the formulas discussed but I often have to see how it works in a situation specific to me before I can start applying it to other situations. I appreciate any help you are able to give.

DeDe

 

[Fluff]

For column D how about

=IF(COUNTIFS(A$2:A2,A2)=1,TEXTJOIN(", ",,FILTER(B2:B100,(A2:A100=A2)*(C2:C100=1))),"")

and col E

=IF(COUNTIFS(A$2:A2,A2)=1,TEXTJOIN(", ",,FILTER(B2:B100,A2:A100=A2)),"")

Although you cannot delete the data from cols B & C

 

[dconnors]

Fluff - this is great, I would have never thought to use anything this advanced, I really appreciate your help. They should be able to filter out the blanks in the "All Orgs" column.

 

[Fluff]

You're welcome & thanks for the feedback.