Google Sheet, Concatenate, Add and Append?

huangapple go评论70阅读模式
英文:

Google Sheet, Concatenate, Add and Append?

问题

我不确定这是否可能...
我有一个用户设备列表(平板电脑、手机等),这些设备处于不同的状态(已擦除、已锁定等)。

是否有可能创建一个公式,可以将每个单元格中的这些状态合并到一个单元格中,同时计算重复项的数量,并将计数结果添加到该单元格,然后使用XLOOKUP将其放在工作表的不同标签中?

目前我是手动完成这个任务,合并是简单的,但最终我得到的是一个单元格中包含以下内容:
Pending Wipe, Locked, Deprovisioned, Locked, Locked, Wiped
我想要去除重复项,只显示如第二张截图中所示的内容。

这是用户设备列表:
Google Sheet, Concatenate, Add and Append?

这是我要求的输出:
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
Google Sheet, Concatenate, Add and Append?

And here is the ouput I'm looking for
Google Sheet, Concatenate, Add and Append?

答案1

得分: 0

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

英文:

Try the following formula-

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

Google Sheet, Concatenate, Add and Append?

huangapple
  • 本文由 发表于 2023年2月10日 16:39:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/75408671.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定