vba Excel 如果其他单元格匹配，则合并单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8977925/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Merge cells if other cell matches
提问by user745778
Merge cells if another cell has two or more of same value: My data has two or more records that are the same except for one cell: example A1 Name=Joe, A2=Joe but B1=Tuesday, B2=Wednesday. I need to show one row A1=Joe and B1=Tuesday,Wednesday. I can create another row like C1=B1&B2, but how do I make it loop through A and if there is a matching record, merge the two values that are in B. I think it will be something like putting into C1,C2, C3... =IF(A1=A2..., B1&B2, " ") But instead of A2 how can it loop through all A then merger the results of B for matching records?
如果另一个单元格具有两个或多个相同值，则合并单元格：我的数据有两个或多个相同的记录，除了一个单元格：示例 A1 Name=Joe，A2=Joe 但 B1=Tuesday，B2=Wednesday。我需要显示一行 A1=Joe 和 B1=Tuesday,Wednesday。我可以创建像 C1=B1&B2 这样的另一行，但是如何让它循环通过 A，如果有匹配的记录，则合并 B 中的两个值。我认为这类似于放入 C1、C2、C3 ... =IF(A1=A2..., B1&B2, " ") 但是它如何代替 A2 遍历所有 A 然后合并 B 的结果以匹配记录？
回答by Skytunnel
I found another solution which does not require the use of Array Formulas, and should be able to loop through all possibilities. Again, it will require your raw data to be sorted by Name (so that all Names are grouped together), then add this formula to the column directly after your Raw data (I'm assuming C2)
我找到了另一个不需要使用数组公式的解决方案，并且应该能够遍历所有可能性。同样，它将要求您的原始数据按名称排序（以便所有名称组合在一起），然后将此公式直接添加到原始数据之后的列中（我假设为 C2）
=IF(A1<>A2,B2,C1&","&B2)
This will Join everything above it with a comma, and reset the join every time the Name changes. By this manor the one with the data you want will always be against the last instance of the Name. We can find the last instance using the results from Steps 2 & 3 in my previous answer, and then index that against the results from the above formula, so..
这将使用逗号连接其上方的所有内容，并在每次名称更改时重置连接。通过这个庄园，拥有您想要的数据的人将始终反对 Name 的最后一个实例。我们可以使用我之前回答中第 2 步和第 3 步的结果找到最后一个实例，然后根据上述公式的结果对其进行索引，因此..
=INDEX(Sheet1!$C:$C,COUNTIF(Sheet1!$A:$A,$A2)+MATCH($A2,Sheet1!$A:$A,0) -1)
In the above I'm assuming the name you want the final answer to is in Cell A2
在上面我假设您想要最终答案的名称在单元格 A2 中
回答by Skytunnel
Here's a possible solution. (I'm hoping I'v understood your setup correctly)
这是一个可能的解决方案。（我希望我已经正确理解了您的设置）
Step 1) Separate out all your unique names in column A into a new sheet, again into column A on your new sheet - if your using excel 2007 or later you can just copy the whole column over then use the Remove Duplicates tool
步骤 1) 将 A 列中的所有唯一名称分离到新工作表中，再次放入新工作表上的 A 列中 - 如果您使用的是 excel 2007 或更高版本，则只需复制整个列，然后使用删除重复项工具
Step 2) On your new sheet add the following into column B2. This will count the number of occurrences of each Name. (Note: I'm assuming your Raw data is in Sheet1, and the new copy to process in Sheet2)
步骤 2) 在您的新工作表上，将以下内容添加到 B2 列中。这将计算每个名称的出现次数。（注意：我假设您的原始数据在 Sheet1 中，而要在 Sheet2 中处理的新副本）
=COUNTIF(Sheet1!$A:$A,$A2)
Step 3) Add the following to C3. This will find the row number of the first occurrence of the name. (IMPORTANT: this means your Raw data will need to have a Sort applied to ensure the Names all appear grouped together)
步骤3）将以下内容添加到C3。这将找到名称第一次出现的行号。（重要提示：这意味着您的原始数据需要应用排序以确保所有名称都显示为一组）
=MATCH($A2,Sheet1!$A:$A,0)
Step 4) On Columns D to J you'll need to add the following Array Formula. If you've not worked with Array Formula before then: (i) select cells D2:J2 (ii) ensuring D2 is the active cell (iii) enter the formula below into the formula bar (iv) press ctrl+shift+enter
步骤 4) 在 D 到 J 列上，您需要添加以下数组公式。如果您之前没有使用过数组公式：(i) 选择单元格 D2:J2 (ii) 确保 D2 是活动单元格 (iii) 在公式栏中输入下面的公式 (iv) 按 ctrl+shift+enter
This takes your data in column B offsets it by the first row occurrence (Step 3), re-sized by the count of names (Step 2), and transposed (which reverses rows for columns)
这会将您在 B 列中的数据按第一行出现偏移（步骤 3），按名称计数重新调整大小（步骤 2），并转置（反转列的行）
=TRANSPOSE(OFFSET(Sheet1!$B:$B,C2-1,0,B2))
That may suit you needs as is, but if you insist on comma separating the days, then you can add this to K2, and just hide columns B to J.
这可能适合您的需求，但如果您坚持使用逗号分隔日期，那么您可以将其添加到 K2，并将 B 列隐藏到 J。
=IF(ISNA(D2),"",D2)&IF(ISNA(E2),"",","&E2)&IF(ISNA(F2),"",","&F2)&IF(ISNA(G2),"",","&G2)&IF(ISNA(H2),"",","&H2)&IF(ISNA(I2),"",","&I2)&IF(ISNA(J2),"",","&J2)
I have assumed that you'll never need to join more than 7 values together (eg the 7 days of the week). But if you do, then you'll have to extend the Array formula onto more columns. If you don't have a set max, then it may be worth investigating a VBA solution.
我假设您永远不需要将超过 7 个值连接在一起（例如一周中的 7 天）。但如果这样做，则必须将数组公式扩展到更多列。如果您没有设置最大值，那么可能值得研究 VBA 解决方案。
