Extract a unique list that ignores a specific value

[mateo315]

Hello all,

This is my first post (hooray!). I have an array formula that extracts a unique list from another column; however, I would like to ignore a specific value and it not populate the cell. For instance:

D
10| XXXXXXXX406
11| XXXXXXXX400
12| Lunch
13| XXXXXXXX933
14| XXXXXXXX400

K
10|
11| XXXXXXXX406
12| XXXXXXXX400
13| XXXXXXXX933
14|


The array formula I have in cell K11 is as follows:

=IF(IFERROR(INDEX($D$10:$D$25,MATCH(0,COUNTIF($K$10:K10,$D$10:$D$25),0)),"")=0,"",IFERROR(INDEX($D$10:$D$25,MATCH(0,COUNTIF($K$10:K10,$D$10:$D$25),0)),""))

If D10:D25 has a value of "Lunch", I would like it to be excluded. I have tried to use the IF function to accomplish this, and it kind of works. However, any values in cells below the instance of "Lunch" is also excluded. For instance:

D
10| XXXXXXXX406
11| XXXXXXXX400
12| Lunch
13| XXXXXXXX933
14| XXXXXXXX400

K
10|
11| XXXXXXXX406
12| XXXXXXXX400
13|
14|

I would like to keep the cell empty if there is no new unique cells, and "Lunch" is excluded.

Thank you in advance for all of your help. I look forward to seeing what the bunch of smart people on this forum comes up with.

Cheers,
Mateo315

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

To add a file to a new post

To add a file to an existing post.

[mateo315]

I have attached a sample of the worksheet. Thank you very much for your help.

[Glenn Kennedy]

I couldn't see anything in D and K of your attached sheet; so I went back to post 1.

I'm not sure; but is this what you wanted in K10, copied down:

Please Login or Register  to view this content.

[Glenn Kennedy]

...I just noted that you may not want the answer alphabetically sorted (which the first solution does). This array formula will leave the unque non-Lunch codes in their orignal order:

=IFERROR(INDEX($D$10:$D$14,MATCH(0,INDEX(IF($D$10:$D$14<>"Lunch",COUNTIF($L$9:$L9,$D$10:$D$14)),0),0)),"")

[mateo315]

Through more tinkering, I was able to accomplish what I was looking for with the following formula:

=IF(IFERROR(INDEX($D$10:$D$25,MATCH(0,IF($D$10:$D$25="Lunch",1,COUNTIF(K$10:$K10,$D$10:$D$25)),0)),"")=0,"",IFERROR(INDEX($D$10:$D$25,MATCH(0,IF($D$10:$D$25="Lunch",1,COUNTIF(K$10:$K10,$D$10:$D$25)),0)),""))

Thank you very much for your help!!!

[mateo315]

It looks as though we came to a very similar conclusion.

[Glenn Kennedy]

Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Aladin Akyurek]

In K10 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Data,SMALL(IF(FREQUENCY(IF(1-(Data=""),IF(1-(Data="Lunch"),
MATCH(Data,Data,0))),Ivec),Ivec),ROWS($K$10:K10))),"")

where D10:D14 >> Data and ROW(Data)-ROW(INDEX(Data,1,1))+1 >> Ivec.