将某些行连接到一个单元格中，忽略重复项 [英] Concatenate certain rows into a cell ignoring duplicates

问题描述

我有一个Google表单，我想在Google表单上的单独表单中对它的回复进行排序.表单的结果看起来像这样.

I have a google form and I would like to sort it's responses in a separate sheet on google sheets. The results of the form look sort of like this.

Id    Job
1     Shelving, Sorting
2     Sorting
1     Cleaning, Shelving
3     Customer Service
2     Shelving, Sorting

我想将其格式化为

Id    Jobs
1     Cleaning, Shelving, Sorting
2     Shelving, Sorting
3     Customer Service

是否存在可以用来完成此操作的公式，请注意它会忽略重复项并将不同的ID分组?作业的顺序无关紧要.

Is there a formula I can use to accomplish this, noting that it ignores duplicates and groups the different ids? Ordering of the jobs does not matter.

推荐答案

此处的示例.

代码类似于:

=unique(transpose(split(join(", ",filter(B1:B10,A1:A10=1)),", ")))

其中

• filter(B1:B10，A1:A10 = 1)为您提供A = 1的所有B值
• join(，"，filter(...))用，"分隔符(例如，"apple，orange"和"kiwi")合并为"apple，orange，kiwi""
• split(join(...)) 将列表拆分到一个数组中(例如，返回到[apple，orange，kiwi]
• transpose(split(...)) 将水平列表转换为垂直列表
• unique(transpose(...))为您提供

• filter(B1:B10,A1:A10=1) gives you all the B values where A = 1
• join(",", filter(...)) joins the list with the ", " separator (e.g. "apple, orange" and "kiwi" becomes "apple, orange, kiwi"
• split(join(...)) splits the list into an array (e.g. back to [apple, orange, kiwi]
• transpose(split(...)) converts the horizontal list to vertical list
• unique(transpose(...)) gives you the unique values (unique() only works with vertical list)

此后，您需要转置，然后加入列表

After this, you need to transpose then join the list

请注意，您必须使分隔符保持一致(例如，始终为，"或，")

Note you must keep the separator consistent (e.g. always "," or ", ")

这篇关于将某些行连接到一个单元格中，忽略重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！