Google表格功能可对行进行分组和合并 [英] Google Sheets function to group and concat rows
问题描述
样本表:
或:
= ARRAYFORMULA(IFNA(VLOOKUP(UNIQUE(A2:A),REGEXREPLACE(TRIM(SPLIT(TRANSPOSE(QUERY(QUERY({A2:A&♦",B2:B&,"},选择最大(Col2)其中Col1!=''按Col2分组枢轴Col1),, 999 ^ 99)),"♦)),",$,),{1,2},0))))
Sample sheet: https://docs.google.com/spreadsheets/d/1AeP0sxDi0-3aaesUdCNTKfricIimjTMFaKO-FX9_g50/edit?usp=sharing
I am trying to find a formula that will group a table on a column and concat the values from all the rows in another column.
For example, if this is my table:
| name | value |
|-------|---------|
| one | alpha |
| two | bravo |
| three | charlie |
| one | delta |
| two | echo |
| four | foxtrot |
| two | golf |
| three | hotel |
| four | india |
This is what I want the formula to output:
| one | alpha, delta |
| two | bravo, echo, golf |
| three | charlie, hotel |
| four | foxtrot, india |
I wish I could share some formula that gets me close but I can't find anything. I thought maybe this formula but, as you can see from the sample sheet, it does not work.
=ARRAYFORMULA(JOIN(", ", TRANSPOSE(FILTER(B2:B, A2:A = {UNIQUE(A2:A)}))))
My thought was, get a unique list of values in the name
column, and then use arrayformula
to get a list of values in the value
column where the name
column equals each value in the unique list. :/
try:
=ARRAYFORMULA(REGEXREPLACE(TRIM(SPLIT(TRANSPOSE(
QUERY(QUERY({A2:A&"♦", B2:B&","},
"select max(Col2)
where Col1 !=''
group by Col2
pivot Col1"),,999^99)), "♦")), ",$", ))
or:
=ARRAYFORMULA(IFNA(VLOOKUP(UNIQUE(A2:A),
REGEXREPLACE(TRIM(SPLIT(TRANSPOSE(
QUERY(QUERY({A2:A&"♦", B2:B&","},
"select max(Col2)
where Col1 !=''
group by Col2
pivot Col1"),,999^99)), "♦")), ",$", ), {1, 2}, 0)))
这篇关于Google表格功能可对行进行分组和合并的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!