How to extract and list unique values from a range excluding blanks and zeros

[Mark McInerney]

Hi All,

I have a range of data a1:a10000.

In this range there are duplicate values, unique values, blanks and zeros. I am looking for a formula that I can copy fill from B1 that will list only unique values, whilst excluding zeros and blanks.

A1 = Book
A2 = Book
A3 =
A4 = 0
A5 = Toy
A6 = Farm
A7 = 0
A8 = Book
A9 = Toy

I want the data that extends from cell B1 to be

B1 = Book
B2 = Toy
B3 = Farm

Is there a formula that I can copy fill to give this result.

Any Help greatly appreciated.

Thanks - Mark.

 

[Eric W]

Try:

Book1
	A	B
1	Book	Book
2	Book	Toy
3		Farm
4	0
5	Toy
6	Farm
7	0
8	Book
9	Toy
10
Sheet3

Cell Formulas
Range	Formula
B1	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$1:A$10000)/(MATCH(A$1:A$10000,A$1:A$10000,0)=ROW(A$1:A$10000)-ROW(A$1)+1)/(A$1:A$10000<>"")/(A$1:A$10000<>0),ROWS($B$1:$B1))),"")

 

[Mark McInerney]

Hi Eric,

Thanks for taking the time to review this - works perfectly. Really appreciated.

Best - Mark.

 

[Mark McInerney]

Hi Eric,

Would it be possible to include a further check on the formula

A B
1 = Book Fiction
2 = Book Horror
3 =
4 = 0
5 = Toy Game
6 = Farm Cow
7 = 0
8 = Book Autobiography
9 = Toy ActionMan

In the formula you sent me it returns the list of unique values - it is perfect. I now want a drop down of these values, and from this it will return the unique values from the second column.

if I have drop down list in C1 which is linked to the first formula you sent me, is there a way for me to build another list (as before, unique values, excluding zeros and blanks), that fills from Cell C2.

e.g. if the value in cell C1 = Book, the values from c2 would fill like this:

A B C
1 = Book Fiction Book
2 = Book Horror Fiction
3 = Horror
4 = 0 Autobiography
5 = Toy Game
6 = Farm Cow
7 = 0
8 = Book Autobiography
9 = Toy ActionMan

Is this possible?

 

[Eric W]

I'm sure it's possible, but the formatting of your example makes it very difficult for me to figure out exactly what you want. Consider using the HTML Maker in my signature to make future examples to read.

Which of the 3 examples below is most like what you want?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Book Fiction	Book Fiction	Book		Book	Fiction	Book	Book		Type/Item	Unique Types	Book
2	Book Horror	Book Horror	Fiction		Book	Horror	Toy	Fiction		Book Fiction	Book	Fiction
3		Toy Game	Horror				Farm	Horror		Book Horror	Toy	Horror
4	0	Farm Cow	Autobiography		0			Autobiography			Farm	Autobiography
5	Toy Game	Book Autobiography			Toy	Game				0
6	Farm Cow	Toy Actionman			Farm	Cow				Toy Game
7	0				0					Farm Cow
8	Book Autobiography				Book	Autobiography				0
9	Toy Actionman				Toy	Actionman				Book Autobiography
10										Toy Actionman
Sheet4

The A:C example has a list of your items in A, the B column has a unique list of the items, C1 would have a dropdown containing Book,Toy,Farm, and the C2:C4 list would list the items that start with what's in C1. How you generate the list in C1 is undetermined at this point.

Example 2 has the category in column E and the specific item in column F. We use the unique formula in column G to get a list of categories. H1 uses that list in the dropdown. Then H2 lists the individual items starting with H1.

Example 3 has the combined category/item list like example 1. I use a somewhat different unique formula in K2:K4 to get a list of categories, by which I mean, the first word of each cell in J2:J10. The headers in J1:K1 would be required in this case. Then the L1 dropdown uses the K2:K4 list as a source, and the L2:L4 list is generated like example 2.

 

[Mark McInerney]

I'm sure it's possible, but the formatting of your example makes it very difficult for me to figure out exactly what you want. Consider using the HTML Maker in my signature to make future examples to read.

Which of the 3 examples below is most like what you want?

	A	B	C	D	E	F	G	H	I	J	K	L
Book Fiction	Book Fiction	Book	Book	Fiction	Book	Book	Type/Item	Unique Types	Book
Book Horror	Book Horror	Fiction	Book	Horror	Toy	Fiction	Book Fiction	Book	Fiction
Toy Game	Horror	Farm	Horror	Book Horror	Toy	Horror
Farm Cow	Autobiography	Autobiography	Farm	Autobiography
Toy Game	Book Autobiography	Toy	Game
Farm Cow	Toy Actionman	Farm	Cow	Toy Game
		Farm Cow
Book Autobiography	Book	Autobiography
Toy Actionman	Toy	Actionman	Book Autobiography
Toy Actionman

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

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

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

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

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

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

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

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

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

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

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

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

[TD="align: right"][/TD]
[TD="align: right"][/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: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

</tbody>

Sheet4​

The A:C example has a list of your items in A, the B column has a unique list of the items, C1 would have a dropdown containing Book,Toy,Farm, and the C2:C4 list would list the items that start with what's in C1. How you generate the list in C1 is undetermined at this point.

Example 2 has the category in column E and the specific item in column F. We use the unique formula in column G to get a list of categories. H1 uses that list in the dropdown. Then H2 lists the individual items starting with H1.

Example 3 has the combined category/item list like example 1. I use a somewhat different unique formula in K2:K4 to get a list of categories, by which I mean, the first word of each cell in J2:J10. The headers in J1:K1 would be required in this case. Then the L1 dropdown uses the K2:K4 list as a source, and the L2:L4 list is generated like example 2.

Hi Eric,

Example 3 is the one that I need - apologies for making this harder than it already is.

Thanks again for taking the time to review this - appreciated.

 

[Mark McInerney]

Hi Eric,

Just to make sure we are on the same page - the type/item that you have in example 3 appears to have the values in the one cell - column J. My data would have a separate column for the "type" and another one for the "Item". the previous formula you sent me works perfectly for extracting the unique "types". I now need to be able to select from a drop down list, any of these "types", which I can manage, and i need to extract the unique "items" that are linked to that "type".

 

[Eric W]

That seems to be closer to my example 2. I'll write something up shortly.

 

[Mark McInerney]

Hi Eric,

You're right - you are now demonstrating that you have a better understanding of my query than I do - apologies.

Thanks again - Mark.

 

[Eric W]

OK, try this:

Book1
	A	B	C	D
1	Book	Fiction	Book	Book
2	Book	Horror	Toy	Fiction
3			Farm	Horror
4	0			Autobiography
5	Toy	Game
6	Farm	Cow
7	0
8	Book	Autobiography
9	Toy	Actionman
Sheet3

Cell Formulas
Range	Formula
C1	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$1:A$10000)/(MATCH(A$1:A$10000,A$1:A$10000,0)=ROW(A$1:A$10000)-ROW(A$1)+1)/(A$1:A$10000<>"")/(A$1:A$10000<>0),ROWS($C$1:$C1))),"")
D2	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$1:B$10000)/(A$1:A$10000=$D$1),ROWS($D$2:$D2))),"")

Incidentally, the formula I used in the Data Validation rule in D1 is:

=OFFSET(C1,0,0,SUMPRODUCT(--(C1:C10000<>"")))