Search code examples
google-sheetsgoogle-sheets-formula

Google Sheet, Concatenate, Add and Append?

I'm not sure this is possible... I have a list of users whith devices (Tablet, Phone etc) in various states (Wiped, Locked etc).

Is it possible to have a formula that can take those states in each cell, merge to a single cell but also count up if there are duplicates and add the count figure to the cell and then place that in a different tab in the sheet using an xlookup?

At the moment I'm doing this by hand, the concatenate is simple but I just end up with this in a cell Pending Wipe, Locked, Deprovisioned, Locked, Locked, Wiped I want to remove the duplicates and jsut have it show as in the second screenshot.

Here is the list enter image description here

And here is the ouput I'm looking for enter image description here

Solution

  • Try the following formula-

    =JOIN(", ",UNIQUE(BYROW(FILTER(C2:C,A2:A=E2),LAMBDA(rw,rw & " x" & COUNTIFS(A2:A,E2,C2:C,rw)))))

    enter image description here