Slight change to formula

[ExcelRoy]

Hi,

Can someone please assist me in changing the following formula to work if the criteria in Z2 is anything other than "SCT"

=IF($Z$2<>"",IFERROR(INDEX(Backorders!$G$2:$G$1000,SMALL(IF($L$2:$L$1000=$Z$2,ROW($L$2:$L$1000)-ROW($L$2)+1),ROWS(AJ$2:AJ2))),""),"")

Many thanks

 

[admiral100]

AA2 house anything BUT "SCT" and Z2 house "SCT"

=IF($Z$2<>"SCT",IFERROR(INDEX(Backorders!$G$2:$G$1000,SMALL(IF($L$2:$L$1000=$AA$2,ROW($L$2:$L$1000)-ROW($L$2)+1),ROWS(AJ$2:AJ2))),""),"")

 

[ExcelRoy]

Hi Aladin,

Edited:

It lists the non-SCT's, is there a way to have them in one lists with no blanks between them just like the SCT list



Many thanks for your help

 

[ExcelRoy]

hi Admiral,

There may be more than one different Non-SCT entry so may be restricted to one cell

Thanks for trying though

 

[Aladin Akyurek]

Hi Aladin,

Almost, the Non SCT lists brings up SCT's unfortunately

So close

Many thanks for your help

Let Z2 equal SCT

Now enter in AJ2 or some other cell more convenient...

Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Backorders!$G$2:$G$1000,SMALL(IF(1-($L$2:$L$1000=$Z$2),ROW($L$2:$L$1000)-ROW($L$2)+1),ROWS(AJ$2:AJ2))),"")

This should really produce a non-SCT sublist...

​

 

[ExcelRoy]

Hi Aladin,

Sorry I did edit my post, but not quick enough

It produces the non-SCT list but with spaces inbetween them, strangely returning some "0"'s when dragged past row 100

Thanks

 

[Aladin Akyurek]

Hi Aladin,

Sorry I did edit my post, but not quick enough

It produces the non-SCT list but with spaces inbetween them, strangely returning some "0"'s when dragged past row 100

Thanks

Does this eliminate empty/blank L-cells while producing non-SCT sublist?

=IFERROR(INDEX(Backorders!$G$2:$G$1000,SMALL(IF(1-($L$2:$L$1000=$Z$2),IF(1-($L$2:$L$1000=""),ROW($L$2:$L$1000)-ROW($L$2)+1)),ROWS(AJ$2:AJ2))),"")

 

[admiral100]

=IFERROR(INDEX(Backorders!G:G,LARGE(INDEX(IF(($L$2:$L$1000<>$Z$2),IF($L$2:$L$1000<>"",ROW($L$2:$L$1000))),0),ROW(A1))),"")

Control+shift+enter, not just enter

 

[ExcelRoy]

Its genuinely scary the knowledge of you guys, many thanks both of you, both options work perfect

Thank you once again

 

[admiral100]

Glad to help you . It was very challenging


But Aladin is better than me ... he's a pro