Excel合并单元格，如果其他单元格匹配 [英] Excel Merge cells if other cell matches

问题描述

如果另一个单元格具有两个或更多相同的值，则合并单元格：我的数据具有两个或多个与一个单元格相同的记录：示例A1 Name = Joe，A2 = Joe，但B1 = Tuesday，B2 = Wednesday。我需要显示一行A1 = Joe和B1 =星期二，星期三。我可以创建像C1 = B1和B2这样的另一行，但是如何使它循环通过A，如果有一个匹配的记录，合并在B中的两个值。我认为这将是一个像C1，C2 ，C3 ... = IF（A1 = A2 ...，B1& B2，）但是代替A2如何循环遍历所有A，然后合并B的结果来匹配记录？

解决方案

我发现另一个不需要使用Array公式的解决方案，应该可以循环遍历所有的可能性。再次，它将要求您的原始数据按名称排序（以便将所有名称分组在一起），然后在您的原始数据（我假定C2）之后直接将该公式添加到列中

  = IF（A1&A2; B2，C1&，& B2）
  

这将使用逗号加入其上的所有内容，并在每次更改名称时重置联接。通过这个庄园，您所需要的数据将始终与名称的最后一个实例相抵触。我们可以使用第2步和第2步的结果找到最后一个实例。 3在我以前的答案中，然后根据上述公式的结果进行索引，所以..

  = INDEX（Sheet1！ $ C：$ C，COUNTIF（Sheet1！$ A：$ A，$ A2）+ MATCH（$ A2，Sheet1！$ A：$ A，0）-1）
  / pre> 
 
 
在上面我假设你想要的最终答案是在Cell A2中的名字
 
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?
解决方案
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)
=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..
=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

                        
这篇关于Excel合并单元格，如果其他单元格匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！