根据标准连接值 [英] Concatenate values based on criteria

问题描述

我在Excel中有两列数据。第一列是来自测试的问号，第二列是引用正在测试的内容的数字。一些元素在多个问题上进行了测试。我想要做的是列出每个元素测试的问号。例如：

  AB应返回：CD 
 
 1 Q Ref Q Ref 
 2 1 N1 1,3,5 N1 
 3 2 N4 2 N4 
 4 3 N1 4 N3 
 5 4 N3 
 6 5 N1 
  

我想使用公式返回。

我已经返回的问题从一列引用特定标准的另一列的未列出的数值连接到另一列。右边的另一列。

编辑：寻找公式答案，而不是VBA如果可能

编辑：感谢所有的意见，迄今为止。我将看看目前为止所提供的每一种可能的解决方案，并让我们知道我要去哪里。 1,2,3等将需要在同一个单元格中。

解决方案

只是把我的评论放在一个答案中，所以更有意义。

首先排列B列的列A和B.

在C2中，公式如下：

  = IF（B2 = B3，A2&，& C3，A2）
  

然后复制。

然后在列E中添加唯一的引用列表。在D2中：

  = VLOOKUP（E2，$ B $ 2：$ C $ 6,2，FALSE）
  

并复制。

然后可以隐藏列C。

它要求它被排序正确和一个帮助列，但它只保留在公式只有规则。

I have a two column list of data in Excel. The first column being a question number from a test and the second column being a number referencing what is being tested on that question. Some elements are tested on more than one question. What I want to be able to do is to list the question numbers that each element is tested on. For example:

   A            B      Should return:   C            D

1  Q            Ref                     Q            Ref
2  1            N1                      1,3,5        N1
3  2            N4                      2            N4
4  3            N1                      4            N3
5  4            N3
6  5            N1

I want this to be returned using a formula.

Problems I have are returning then concatenating an unspecified number of values from one column that reference to a particular criterion for another column that is further to the right.

EDIT: Looking for a formula answer, not VBA if possible

EDIT: Thanks all for your comments so far. I will have a look at each of the possible solutions given so far and let you know what I go with. The 1,2,3 etc will need to be in the same cell.

解决方案

Just to put my comment in an answer, so it make more sense.

First sort columns A and B on Column B.

In C2 put the formula:

=IF(B2=B3,A2&","&C3,A2)

Then copy down.

Then in Column E place your unique reference list. And in D2 put:

=VLOOKUP(E2,$B$2:$C$6,2,FALSE)

And copy down.

You can then hide column C.

It does require that it be sorted correctly and a helper column but it does stay to the formulas only rule.

这篇关于根据标准连接值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！