Extract list of unique values, only if they have specific text next to them.

Hello,

I am using the following function to retrieve a list of unique values which is working fine:

{D2=INDEX($A$1:$A$50, MATCH(0, COUNTIF($D$1:D1, $A$1:$A$50), 0))}

However, I only wish to return the value if the value in A:A=>0

So if it is something like

A - 0
B - 1
C - 2
D - 1

Returns:

B-1
C-2
D-1

Despite A being a unique in this list, it is skipped because it's value is 0.

I also would like it to not insert a blank in this case, I want it to be the next unique with a value.

Thank you in advance.

hi there. it's not very clear how your data looks like. i don't know if "A - 0" is inside the same cell or are you trying to use the dash to represent different columns. but try these 2 solutions. if they are in the same cell:
=INDEX($A$1:$A$50, MATCH(0, IF(--(MID($A$1:$A$50,FIND("-",$A$1:$A$50)+2,LEN($A$1:$A$50)))>0,COUNTIF($D$1:D1, $A$1:$A$50)), 0))

different cells:
=INDEX($A$1:$A$50, MATCH(0, IF($B$1:$B$50>0,COUNTIF($D$1:D1, $A$1:$A$50)), 0))

both are still array formulas. next time though, do upload an excel sample so that we do not have to manually key in your data to do a testing.

input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.

the upload attachment must be done by going to Go Advanced. click on Manage Attachments. Choose file, upload and close the window.

if i really guessed it wrongly, please upload something according to my recommendations. try to use the solution i have given and we'll help to advise what went wrong.

I am not very clear what the data is or the mix or layout.

This is also confusing. It sounds like a self contradiction.

Despite A being a unique in this list, it is skipped because it's value is 0.

I also would like it to not insert a blank in this case, I want it to be the next unique with a value.

I took a guess. I also guessed that the number of rows is variable.

This formula goes in C1 and filled down.

Formula:

Please Login or Register  to view this content.

	A	B	C
1	A	5	A
2	B	0	C
3	C	2	C
4	D	3	D
5	E	5	E
6	F	1	F

May be try

C2

Formula:

Please Login or Register  to view this content.

Row\Col	A	B	C	D
2	A	0	B	1
3	B	1	D	3
4	C
5	D	3

Copy across !

Glad you found solution. Thank you for the feedback and for marking this thread Solved. It helps.