Create unique list from vlookup or index/match

Question

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

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...

Possible? Reasonable expectations?

Bonus points for explaining how your formula works

Answer 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.

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.

It must be entered with 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.

(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.

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

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.

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

Answer 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.

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

(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.

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'.