查找，匹配和连接 [英] Lookup, Match and Concatenate

问题描述

我需要一个公式/函数来连接一列和多行中的单元格值.匹配条件将应用于其他列.这是我要做的事情的示例:

I need a formula/function to concatenate cell values from one column and multiple rows. The matching criteria is applied to a different column. Here is my example of what I have to do:

Islington | "Bunhill"   | EC2M  
Islington | "Bunhill"   | EC2Y  
Islington | "Bunhill"   | N1  
Barnet    | "Burnt Oak" | HA8  
Barnet    | "Burnt Oak" | NW7  
Barnet    | "Burnt Oak" | NW9  

最终结果应如下所示:

Islington | "Bunhill"   | EC2M, EC2Y, N1  
Barnet    | "Burnt Oak" | HA8, NW7, NW9 

基本上，我需要从第二列中删除所有重复项，但是要保存与每个重复项配对的第三列中的数据，并将其连接在一个单元格中.

Basically, I need to remove all duplicates from the second column, but save the data from the third column that is paired with each of the duplicates, and concatenate it in one cell.

推荐答案

您可以使用函数来逐步完成各个步骤.从UNIQUE功能开始.将其放在方便列出B列的所有唯一值的单元格中:

You can go through a process of steps using functions. Start with the UNIQUE function. Put this in a cell where it is convenient to list all the unique values of column B:

=UNIQUE(B:B)

获取列B中的所有唯一值. Google支持-唯一功能

Gets all the unique values in column B. Google Support - Unique Function

UNIQUE函数的结果将如下所示:

The result from the UNIQUE function will look like this:

现在您已拥有B列中的所有唯一值，则可以使用FILTER函数检索与该唯一值匹配的所有行.

Now that you have all the unique values from column B, you can use the FILTER function to retrieve all the rows that match that unique value.

=FILTER(D1:D6, B1:B6=A8)

FILTER函数将所有结果列在该列下，但是您可以使用CONCATENATE函数来避免这种情况.

The FILTER function lists all the results down the column, but you can use the CONCATENATE function to avoid that.

FILTER函数的结果:

Results of FILTER function:

CONCATENATE的结果:

Results of CONCATENATE:

您将需要调整FILTER函数以现在使用D列而不是C列.

You will need to adjust the FILTER function to now use column D, rather than column C.

=CONCATENATE(FILTER(D1:D6, B1:B6=A8))

这解决了获取多行数据的问题，但是现在值之间没有分隔符.

This solves the problem of getting data in multiple rows, but now there is no separator between the values.

要解决该问题，您可以创建第四列，并在其末尾添加一个逗号函数:

To get around that problem, you can create a fourth column with a function that adds a comma to the end:

最后有一个多余的逗号问题，您可以使用LEFT函数将其消除:

There is a problem with an extra comma on the end, which you can get rid of with the LEFT function:

这篇关于查找，匹配和连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！