英文:
How to combine multiple rows into one row based on matching names in Google Sheets?
问题
所以我正在将一个数据库导入到Google Sheets中，它将所有条目都放在单独的行中，而不是像我需要的那样放在一个单独的行中，以便与我的其他数据一起使用。我正在尝试找出是否有一个可以用来创建下面我所需要的动态的公式：
导入：
Name | Group | Purchase | Quantity
Mike Smith | A | Apples | 4
John Matthews | B | Oranges | 10
John Matthews | B | Peaches | 2
John Matthews | B | Oranges | 8
Yanni Karlo | A | Limes | 23
Yanni Karlo | A | Apples | 15
理想结果：
Name | Group | Purchase 1 | Quantity 1 | Purchase 2 | Quantity 2 | Purchase 3 | Quantity 3
Mike Smith | A | Apples | 4 | | | |
John Matthews | B | Oranges | 10 | Peaches | 2 | Oranges | 8
Yanni Karlo | A | Limes | 23 | Apples | 15 | |
在网上搜索时，找到了许多将它们合并到单元格中的方法以及其他类似的方法，但我似乎还没有找到如何使数据透视表在保留前两列的同时工作的方法。同时也希望这个方法是原生于Google Sheets而不是通过附加组件的方式实现的。
英文:
So I'm importing a database into Google Sheets, and it put all the entries in separate rows instead of a singular row like I need it to be to work with the rest of my data. I'm trying to figure out if there is a formula that can be used to create the dynamic I'm looking for below:
Import:
Name | Group | Purchase | Quanity
Mike Smith | A | Apples | 4
John Matthews | B | Oranges | 10
John Matthews | B | Peaches | 2
John Matthews | B | Oranges | 8
Yanni Karlo | A | Limes | 23
Yanni Karlo | A | Apples | 15
Ideal Result:
Name | Group | Purchase 1 | Quanity 1 | Purchase 2 | Quanity 2 | Purchase 3 | Quanity 3
Mike Smith | A | Apples | 4 | | | |
John Matthews | B | Oranges | 10 | Peaches | 2 | Oranges | 8
Yanni Karlo | A | Limes | 23 | Apples | 15 | |
Went looking online, found many ways to combine them into a cell and other things of the sort, but I guess I haven't been able to figure how to get a pivot to work while keeping the first two col in tact. Also looking for this to be native to sheets and not through an add-on.
答案1
得分: 0
*您可以尝试：*
=map(unique(tocol(A2:A,1)),lambda(Σ,{Σ,xlookup(Σ,A:A,B:B,),torow(filter(C:D,A:A=Σ))}))
[![输入图像描述][1]][1]
[1]: https://i.stack.imgur.com/P8fVo.png
英文:
<!-- language-all: js -->
You may try:
=map(unique(tocol(A2:A,1)),lambda(Σ,{Σ,xlookup(Σ,A:A,B:B,),torow(filter(C:D,A:A=Σ))}))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论