根据另一行的条件在一行中获取多个值转置 [英] Get multiple value in one row transpose based on criteria on another row

问题描述

我有一张从 Google 表单填充的工作表.它带有一个公司名称列和一个员工姓名.company 列可以具有相同或重复的值，但员工列没有.那么，我如何才能获得一个公司名称，其中包含所有员工姓名?

解决方案

您可以查看

<小时>

公式在D2中:

=UNIQUE(A2:A)

<小时>

公式在E2中:

• 选项 1

  =TRANSPOSE(过滤器(B:B,A:A=D2))

• 选项 2，上面的方法会很快变得缓慢，所以也许可以改用:

  =TRANSPOSE(FILTER(B$2:INDEX(B:B,COUNTA(B:B)),A$2:INDEX(A:A,COUNTA(A:A))=D2))

• 选项 3，即使使用

  I have a sheet that populated from Google Form. It comes with a column for the company name, and an employee name. The company column can have the same or duplicate value, but the employee column doesn't. So, how I can get a company name with all the employee name in one row?

  解决方案

  You could look into UNIQUE and FILTER functions if you are using Google Spreadsheet. Here is an example:

  ---

  Formula in D2:

  =UNIQUE(A2:A)
  

  ---

  Formula in E2:

  • Option 1

    =TRANSPOSE(FILTER(B:B,A:A=D2))
    

  • Option 2, the above can get sluggish really quick, so maybe instead use:

    =TRANSPOSE(FILTER(B$2:INDEX(B:B,COUNTA(B:B)),A$2:INDEX(A:A,COUNTA(A:A))=D2))
    

  • Option 3, Even using QUERY is a possibility:

    =TRANSPOSE(QUERY({$A$2:INDEX($B:$B,COUNTA(B:B))},"Select Col2 where Col1='"&D2&"'"))
    

  • Option 4, Found a way to just use a single formula in E2:

    =ARRAYFORMULA(ARRAY_CONSTRAIN(TRIM(SPLIT(TRIM(TRANSPOSE(QUERY(IF(TRANSPOSE(D2:INDEX(D:D,COUNTA(D:D)))=A2:INDEX(A:A,COUNTA(A:A)),B2:INDEX(B:B,COUNTA(B:B))&"|",),,9^99))),"|")),COUNTA(D2:INDEX(D:D,COUNTA(D:D))),100))
    

  This way when you add data to column A:B the formula will auto-update your desired output:

  这篇关于根据另一行的条件在一行中获取多个值转置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！