Unique Values in Array Vlookup Function

[rpm567]

I've been trying to understand the array vlookup approach and have not been successful. If I have a list of item codes and I want to return a list of the unique items horizontally

If starting in B2, I enter this table:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Item[/TD]
[/TR]
[TR]
[TD]Coffee[/TD]
[TD]Espresso[/TD]
[/TR]
[TR]
[TD]Coffee[/TD]
[TD]Espresso[/TD]
[/TR]
[TR]
[TD]Coffee[/TD]
[TD]Cappuccino[/TD]
[/TR]
[TR]
[TD]Soda[/TD]
[TD]Coke[/TD]
[/TR]
[TR]
[TD]Other[/TD]
[TD]Water[/TD]
[/TR]
[TR]
[TD]Coffee[/TD]
[TD]Espresso[/TD]
[/TR]
[TR]
[TD]Coffee[/TD]
[TD]Americano[/TD]
[/TR]
</tbody>[/TABLE]

Then I want to list out, horizontally, the Coffee orders, I use the array formula:

{=IFERROR(INDEX($B$2:$C$9,SMALL(IF($B$2:$B$9=$E2,ROW($B$2:$B$9)-1),COLUMNS($F2:F2)),2),"")}

Where cell E2 is Coffee (or could be one of the other categories).

My issue is that there are a ton of orders and I would like to edit this so that I only return unique values.

It takes all of my excel skill and brain power to understand the equation I wrote, if someone can offer the solution for unique values and also a brief explanation as to how it works, it would be greatly appreciated. Thanks in advance!

 

[CyrusTheVirus]

Hi rpm,

Well, firstly, if you are looking for an easier way to do this, you could copy down the items and paste them in a separate column, then remove duplicates, and then copy the range then paste special 'transpose' the data.

But if you are set on a formula, the I think a helper column could be useful (I use them all the time). Try the below setup.

How my formulas work are by:

D2: Counts how many Items are within the range. It locks on Cell C2 that way it increments as it is copied down. I think it'll make sense if you take a look at it.

F2: Counts how many 1's are in the helper column. Because we only want to extract the names with a one next to them, since those are the first of the numbers for the names, anything name with a number higher than 1 next to it is a duplicate and should be ignored in our final formula.

G4: IF(COLUMNS($G$4:J$4)>$F$2,"" <--- means, if the number of columns the formula copies over to is greater than the count of 1's in the helper column, then blank, ELSE

INDEX($C$2:$C$8,SMALL(IF($D$2:$D$8=1,ROW($C$2:$C$8)-ROW($C$2)+1),COLUMNS($G$4:J$4))) <---- means, well, i think you should evaluate this formula (Alt, T, U, F) and take a look for yourself and then reply with specific questions about it. Sorry, I actually have to go eat. Hope this has helped....

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L
1	Category	Item	Helper		Count of 1's
2	Coffee	Espresso	1		5
3	Coffee	Espresso	2
4	Coffee	Cappuccino	1		Formula	Espresso	Cappuccino	Coke	Water	Americano
5	Soda	Coke	1
6	Other	Water	1
7	Coffee	Espresso	3
8	Coffee	Americano	1
Sheet2

 

[DRSteele]

I think I can help a bit. Check out this thread in the Forum, especially the video, for some ideas on how to extract a unique list based on criteria.

http://www.mrexcel.com/forum/excel-...ws-based-numerous-conditions.html#post4643968

Also this: http://www.mrexcel.com/forum/excel-questions/942043-data-validation-list.html#post4525625

Here I've done the report in rows, but you could shift to columns if necessary. Copy A17 down as far as or farther than indicated by the unique count indicated in B13.

	A	B	C
Coffee	Espresso
Coffee	Espresso
Coffee	Cappuccino
Soda	Coke
Other	Water
Coffee	Espresso
Coffee	Americano
Coffee (unique)

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]Category[/TD]
[TD="bgcolor: #FFF2CC"]Item[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: #C6E0B4, align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFFF00"]Coffee[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FFF2CC"]Coffee (unique)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #C6E0B4"]Espresso[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #C6E0B4"]Cappuccino[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #C6E0B4"]Americano[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #C6E0B4"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet22​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B13[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF($B$2:$B$8<>"",IF($B$2:$B$8=$A$15,MATCH($B$2:$B$8&$C$2:$C$8,$B$2:$B$8&$C$2:$C$8,0))),ROW($B$2:$B$8)-ROW($B$2)+1),1))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A17[/TH]
[TD="align: left"]{=IF(ROWS(A$17:A17)>$B$13,"",INDEX($C$2:$C$8,SMALL(IF(FREQUENCY(IF($B$2:$B$8<>"",IF($B$2:$B$8=$A$15,MATCH($B$2:$B$8&$C$2:$C$8,$B$2:$B$8&$C$2:$C$8,0))),ROW($B$2:$B$8)-ROW($B$2)+1),ROW($B$2:$B$8)-ROW($B$2)+1),ROWS(A$17:A17))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.[/TD]
[/TR]
</tbody>[/TABLE]

 

[FlameRetired]

Another similar way.

With a drop down (or just type in the category) in E1 and this helper formula in D2 copied down.

=IF(B2="","",IF(COUNTIFS($B$2:B2,$E$1,$C$2:C2,C2)=1,ROWS($2:2),""))

Enter this in F3 and fill across.

=IFERROR(INDEX($C$2:$C$100,SMALL($D$2:$D$100,COLUMNS($F:F))),"")

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr][tr][td]

1​

[/td][td]Category[/td][td]Item[/td][td][/td][td]Coffee[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]Coffee[/td][td]Espresso[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

3​

[/td][td]Coffee[/td][td]Espresso[/td][td][/td][td][/td][td]Espresso[/td][td]Cappuccino[/td][td]Americano[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]Coffee[/td][td]Cappuccino[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]Soda[/td][td]Coke[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]Other[/td][td]Water[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]Coffee[/td][td]Espresso[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]Coffee[/td][td]Americano[/td][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]