Excel-合并具有通用值的行并将一列中的差异连接起来 [英] Excel - Merge rows with common values and concatenate the differences in one column

问题描述

我想将具有共同值的行合并，并将其差异连接到一列中.

I would like to merge rows with common values and concatenate the differences in one column.

我认为最简单的方法是向您展示一个例子.

I think the easiest thing to do is show you an example.

输入:

Customer Name   |   NEW YORK    |   ALBANY 
Customer Name   |   NEW YORK    |   CLINTON    
Customer Name   |   NEW YORK    |   COLUMBIA
Customer Name   |   NEW YORK    |   DELAWARE
Customer Name   |   NEW YORK    |   DUTCHESS  
Customer Name   |   VERMONT     |   BENNINGTON  
Customer Name   |   VERMONT     |   CALEDONIA
Customer Name   |   VERMONT     |   CHITTENDEN
Customer Name   |   VERMONT     |   ESSEX
Customer Name   |   VERMONT     |   FRANKLIN

所需的输出:

Customer Name   |   VERMONT     |   BENNINGTON,CALEDONIA,CHITTENDEN,ESSEX,FRANKLIN
Customer Name   |   NEW YORK    |   ALBANY,CLINTON,COLUMBIA,DELAWARE,DUTCHESS

我确实看到了其他一些帖子，但是我认为它们不是我想要的.

I did see some other posts on this but I don't think they were exactly what I was trying to do.

推荐答案

在新工作表中，您可以使用这些公式创建唯一的客户名称和状态列表.

In a new sheet, you can use these formulae to create a list of unique customer names and states.

您需要使用两个协调功能，在新工作表的A2

You need to use two coordinating functions, in a new sheet enter the following function in A2

=IFERROR(INDEX(Sheet1!$A$2:$A$6, MATCH(0, COUNTIFS(Sheet2!$A$1:A1, Sheet1!$A$2:$A$6,Sheet2!$B$1:B1, Sheet1!$B$2:$B$6), 0)),"")

和B2

=IFERROR(INDEX(Sheet1!$B$2:$B$6, MATCH(0, COUNTIFS(Sheet2!$A$1:A1, Sheet1!$A$2:$A$6,Sheet2!$B$1:B1, Sheet1!$B$2:$B$6), 0)),"")

要使其正常工作，必须满足以下条件

For this to work, the following needs to be true

1. 这需要放置在第二个工作表中(相应地重命名为Sheet2)
应该修改
2. Sheet1!$A$2:$A$6和Sheet1!$B$2:$B$6(工作表名称和范围)以分别包含您的客户名称和状态的完整列表.如果列表太长，则唯一值列表中的最后一个条目将b 0
3. 以上公式应分别粘贴到Sheet2!A2和Sheet2!B2
4. 这些是数组公式.应该将其输入到指定的单元格中，然后点击ctrl+alt+enter
提交.
5. 将初始公式作为数组输入到A2和B2中之后，将这些公式复制并粘贴到Column A和Column B下方，直到不再显示唯一名称为止.

1. This needs to be placed in a second worksheet (rename Sheet2 accordingly)
2. Sheet1!$A$2:$A$6 and Sheet1!$B$2:$B$6 should be modified (sheet name and range) to contain your full list of customer names and states respectively. If the list is too long, the last entry in your list of unique values will b 0
3. The above formulae should be pasted in Sheet2!A2 and Sheet2!B2 respectively
4. These are array formulae. It should be entered into the designated cell and then submitted by hitting ctrl+alt+enter
5. After entering the initial formulae into A2 and B2 as an array, copy and paste the formulae down Column A and Column B until unique names no longer appear.

(此答案来自此问题.还有一些变体作为其他答案)

(This answer is adopted from this question. There are a few variants as other answers)

使用数组函数返回ColumnC中的县.将公式粘贴为C2中的数组(具有更新的引用)，然后将其复制到列表中.

Use an array function to return counties in ColumnC. Paste the formula in C2 as an array (with updated references), and then copy it down the list.

=TEXTJOIN(", ",TRUE,IF((Sheet2!A2=Sheet1!$A$2:$A$6)*(Sheet2!B2=Sheet1!$B$2:$B$6),Sheet1!$C$2:$C$6,""))

这些功能如何工作的简要说明:

Brief explination on how these functions work:

-功能1:

1. COUNTIFS查看您的源数据，并返回一个数组，如果该数组已经在列表中，则返回1，否则返回一个0.
2. MATCH查看COUNTIFS数组，并标识第一个0
3. INDEX查看您的源数据，并返回由MATCH
标识的行

1. the COUNTIFS looks at your source data, and returns an array with 1 if it is already in the list, and a 0 if it is not.
2. MATCH looks at the COUNTIFS array, and identifies the first 0
3. INDEX looks at your source data, and returns the row identified by MATCH

-Function2:

-Function2:

1. IF函数根据源数据是否匹配行的唯一客户和状态来创建两个TRUE或FALSE数组.
2. 将这些数组相乘，仅当两个条目均为TRUE时，才创建一个新的数组TRUE. (意味着客户和州都匹配)
3. IF语句返回县(如果为true，如果为false，则返回)以及空白单元格""
4. TEXTJOIN过滤出空单元格(这是TRUE的工作)，并将它们与逗号和空格(定界符)连接在一起.

1. The IF function creates two arrays of TRUE or FALSE based on whether the source data matches the unique customer and state of the row.
2. Those arrays are multiplied, creating a new array that is TRUE only if both entries were TRUE. (meaning both the customer and state matched)
3. The IF statement returns the county if true, and if false, and empty cell ""
4. TEXTJOIN filters out the empty cells (That is what the TRUE is doing) and joins them together with a comma and a space (the delimiter).

这篇关于Excel-合并具有通用值的行并将一列中的差异连接起来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！