从vlookup或index / match创建唯一列表

I love VBA but if it can be done with a fairly straight forward formula, that's better! (for the end user's sake)

我喜欢VBA,但如果可以用一个相当直接的公式来完成,那就更好了! (为了最终用户的利益)

Let's say I have the following data:

假设我有以下数据:

I'd like to create a formula I can fill down in column [D] that will lookup on "a" (this could also be a row rather than column) and return in consecutive rows (or columns) "red", "blue", "green" - that is consecutively return all unique values and then stop/error out or something...

我想创建一个我可以在列[D]中填写的公式,它将在“a”上查找(这也可以是一行而不是列)并返回连续的行(或列)“red”,“blue” “,”green“ - 连续返回所有唯一值,然后停止/错误或某事......

Possible? Reasonable expectations?

可能?合理的期望?

Bonus points for explaining how your formula works

用于解释您的公式如何运作的奖励积分

2 个解决方案

#1

There is a good explanation of how to extract a list of distinct values with an array formula here

这里有一个很好的解释,如何使用数组公式提取不同值的列表

The basic formula is this

基本公式是这样的

=IFERROR(INDEX(B$2:B$19, MATCH(0, COUNTIF(D$1:D1, B$2:B$19), 0)),"")

which would give you all of the different values in column B.

这将为您提供B列中的所有不同值。

With some tweaking you can get

通过一些调整,你可以得到

=IFERROR(INDEX(B$2:B$19, MATCH(0, COUNTIF(D$1:D1, IF(A$2:A$19=D$1,B$2:B$19,D$1)), 0)),"")

also as an array formula entered in D2 with "a" in D1 to select just the ones with "a" in column A.

也作为在D2中输入的数组公式,D1中的“a”只选择A列中带有“a”的公式。

It must be entered with CTRL+shift+enter

必须使用CTRL + shift + enter输入

The original formula works by

原始公式的工作原理

(1) Using COUNTIF to create an array where each element corresponds to a cell in the range B2:B19. If the string in the range B2:B19 is already in a cell above the current cell (initially D2), COUNTIF will put a 1 in the array: if not, it will put a zero.

(1)使用COUNTIF创建一个数组,其中每个元素对应于B2:B19范围内的单元格。如果B2:B19范围内的字符串已经位于当前单元格上方的单元格中(最初为D2),则COUNTIF将在数组中放置1:如果不是,则将其置为零。

(2) Using MATCH to find the position of the first zero (corresponding to the next string which hasn't been added to the list) in the array.

(2)使用MATCH找到数组中第一个零(对应于未添加到列表中的下一个字符串)的位置。

(3) Using INDEX to find the string in B2:B19 at that position.

(3)使用INDEX在该位置找到B2:B19中的字符串。

I have chosen to modify it by adding a preliminary step

我选择通过添加一个预备步骤来修改它

(0) If the corresponding letter in A2:A19 is "a", use B2:B19 as before - if not, use "a". "a" is already in D1, so it won't be added to the list in column D.

(0)如果A2:A19中的相应字母是“a”,则像以前一样使用B2:B19 - 如果不是,则使用“a”。 “a”已经在D1中,因此它不会被添加到D列的列表中。

http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

#2

To answer the question using a Pivot table:-

使用数据透视表回答问题: -

(1) Select Insert | Pivot Table from the ribbon. Select A1:19 as the range and D1 as the location, on the existing sheet.

(1)选择Insert |从功能区旋转表。在现有工作表上选择A1:19作为范围,选择D1作为位置。

(2) Drag Letter into the Filter box and Colour into the Row Labels box.

(2)将Letter拖入Filter框并将Color拖入Row Labels框。

(3) Click on the filter symbol next to the table heading, Check Select Multiple Items and make sure only the check box next to "a" is selected.

(3)单击表标题旁边的过滤器符号,选中“选择多个项目”,并确保仅选中“a”旁边的复选框。

You may wish to remove the 'Row Labels' Heading by clicking on Field Headers in the ribbon, and remove the Grand total by right-clicking on it and selecting 'Remove Grand Total'.

您可能希望通过单击功能区中的“字段标题”来删除“行标题”标题,并通过右键单击并选择“删除总计”来删除总计标题。