根据条件连接值 [英] Concatenate values based on criteria

问题描述

我在 Excel 中有一个两列数据列表.第一列是测试中的问题编号，第二列是引用该问题所测试内容的编号.一些元素在不止一个问题上进行测试.我希望能够做的是列出测试每个元素的问题编号.例如:

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

我希望使用公式返回.

我遇到的问题是，然后将一列中的未指定数量的值连接起来，这些值引用了另一列的特定标准，而另一列更靠右.

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.

寻找公式答案，如果可能，不要使用 VBA

Looking for a formula answer, not VBA if possible

感谢大家到目前为止的评论.我将看看到目前为止给出的每个可能的解决方案，并让您知道我的选择.1,2,3 等将需要在同一个单元格中.

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.

首先在 B 列上对 A 列和 B 列进行排序.

First sort columns A and B on Column B.

在 C2 中输入公式:

In C2 put the formula:

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

然后复制下来.

然后在 E 列中放置您的唯一参考列表.在 D2 中放入:

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

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

复制下来.

然后您可以隐藏 C 列.

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屋！