Making a list of cells from two columns excluding certain values

Hi Everyone,

Ok so, what I am trying to do is take two columns (Set1 and Set2) and make one master list so I can make labels to print. I do not want to include the cells with "ZNO" inside.

I cannot use the filter option because I want the information to automatically update when another sheet is changed.

Can someone tell me how I should go about doing this whilst keeping the same order from the original list please?

Thanks in advance
Calum

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW(RNG)*10000+COLUMN(RNG))/(RNG<>"")/(RNG<>$M$1)/($A$1:$L$1=N$1),ROWS($A$1:$A1)),"R0000C0000"),),"")
in M1 ZNO

Hello,

Thank you for your reply, I cannot get that to work.

I was looking for something more simple like the attached. I just want to filter the columns individually but keep the order.

I was using the following but it doesn't keep the order that the cells were originally in;

=INDEX($A$2:$A$112,MATCH(SMALL(COUNTIF($A$2:$A$112,"<="&$A$2:$A$112),ROWS($D$2:D2)),COUNTIF($A$2:$A$112,"<="&$A$2:$A$112),0))

I need the two columns to correspond to each other.

Thanks
Calum