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.

 

[Mark McInerney]

Hi Eric,

I can't thank you enough - this is excellent. You are exceptional - very grateful.

Best - Mark.

 

[Eric W]

Happy to help. Thanks for the feedback.

 

[Mark McInerney]

Hi Eric,

Back again - so sorry. It is working fine, but when I copy fill the formula down, it is pulling in duplicates - I'd love to tell you I know how to remove them....I am out of my depth. Any advice would be appreciated.

Thanks again - Mark.

 

[Eric W]

Change the D2 formula to:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$1:B$10000)/(A$1:A$10000=$D$1)/(COUNTIF($D$1:$D1,$B$1:$B$10000)=0),1)),"")

 

[Mark McInerney]

Change the D2 formula to:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$1:B$10000)/(A$1:A$10000=$D$1)/(COUNTIF($D$1:$D1,$B$1:$B$10000)=0),1)),"")

Hi Eric,

That's working perfectly - Many Thanks - Mark.

 

[Peter_SSs]

Some alternative formulas. I have assumed a heading row exists, or can be made to exist. If that is not possible then further alternatives are available.
data Validation is again in D1

Excel Workbook
	A	B	C	D
1	List 1	List 2	Unique 1	Book
2	Book	Fiction	Book	Fiction
3	Book	Horror	Toy	Horror
4			Farm	Autobiography
5	0
6	Toy	Game
7	Farm	Cow
8	0
9	Book	Autobiography
10	Toy	Actionman
11	Book	Horror
12
Lists

 

[Mark McInerney]

Hi Peter,

Another genius - am jealous as to how you guys come up with these things! I tried it this morning and it too works perfectly. Thank you very much for taking the time to review and respond to my query.

Thanks and appreciation - Best - Mark.

 

[Peter_SSs]

Hi Peter,

Thank you very much for taking the time to review and respond to my query.

Thanks and appreciation - Best - Mark.

You're welcome & thanks for the feedback.