在Excel中按ID合并行 [英] Merge rows by ID in excel

问题描述

我在excel中有一组这样的列:

I've got a set of columns like this in excel:

ID                                      sizeAndColourAndSku_Value
2ff0d3d7-8c18-4386-83be-1b2485fb4ea9    Select size
2ff0d3d7-8c18-4386-83be-1b2485fb4ea9    Extra Small - Available (only 1 left)
2ff0d3d7-8c18-4386-83be-1b2485fb4ea9    Small - Available (only 2 left)
2ff0d3d7-8c18-4386-83be-1b2485fb4ea9    Medium - Available (only 2 left)
2ff0d3d7-8c18-4386-83be-1b2485fb4ea9    Large - Available (only 1 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    Select size
4697ce05-2e50-4e67-85d5-ad733e15ae95    6 - Available (only 2 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    8 - Available (only 4 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    10 - Available (only 6 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    12 - Available (only 6 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    14 - Available (only 4 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    16 - Available (only 2 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    Select size

我需要按其ID合并合并，这样结果就变成了这样:

I need to combine merge them by their ID so the results become like this:

2ff0d3d7-8c18-4386-83be-1b2485fb4ea9   Extra Small  Small  Medium  Large
4697ce05-2e50-4e67-85d5-ad733e15ae95   6      8      10     12      14

我在这里读到，最快，最简单的方法是使用excel中的数据透视表

I read here that the quickest and easiest way to do this is with a pivot table in excel

http://www.excelbanter.com/showthread.php?t=235727

但是，我使用的是excel 2010，但他们提供的方法却没有真正起作用，有人能帮助我解决这个问题吗?

However I'm using excel 2010 and the method they've provided doesn't really work, anyone able to help me out with this?

谢谢

推荐答案

但是在SE的某个位置已经有答案(我写了，但是找不到！)

There is already an answer to this somewhere on SE (I wrote it but can't find it!) however:

1. 使用文本到列和-作为分隔符来解析sizeAndColourAndSku_Value列.
2. 用Select size删除行.
3. 假设Extra Small- Available (only 1 left)的Extra Small部分现在放在B2中:

1. Parse your sizeAndColourAndSku_Value column with Text to Columns and - as the delimiter.
2. Delete rows with Select size.
3. Assuming the Extra Small part of Extra Small- Available (only 1 left) is now in B2 put:

=IF(COLUMN()<COUNTIF($A:$A,$A2)+3,IF($A2=$A3,INDIRECT("$B"&ROW()+COLUMN()-3),""),"")

并横向复制以适合.

1. 选择整个工作表，然后在顶部粘贴特殊值.
2. 在B2中放入:=A1=A2，然后抄下来以适合.
3. 为TRUE过滤ColumnB并删除可见行.
4. 删除ColumnB.

1. Select entire sheet and Paste Special Values over the top.
2. In B2 put: =A1=A2 and copy down to suit.
3. Filter ColumnB for TRUE and delete visible rows.
4. Delete ColumnB.

编辑以添加图像:

这篇关于在Excel中按ID合并行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！