Formula to list missing values

[Brew]

How do i create a formula or macro that will list in the target range ck5:ck13 all values from the list in BW1:CF1, which are not listed in BW5:CF18

 

[Domenic]

Try the following...

CK5, copied down:

=INDEX($BW$1:$CF$1,SMALL(IF(COUNTIF($BW$5:$CF$18,$BW$1:$CF$1)=0,COLUMN($BW$1:$CF$1)-COLUMN($BW$1)+1),ROWS($CK$5:CK5)))

...confirmed with CONTROL+SHIFT+ENTER.

To include an error trap...

CJ5:

=SUMPRODUCT(--(COUNTIF(BW5:CF18,BW1:CF1)=0))

CK5, copied down:

=IF(ROWS($CK$5:CK5)<=$CJ$5,INDEX($BW$1:$CF$1,SMALL(IF(COUNTIF($BW$5:$CF$18,$BW$1:$CF$1)=0,COLUMN($BW$1:$CF$1)-COLUMN($BW$1)+1),ROWS($CK$5:CK5))),"")

...confirmed with CONTROL+SHIFT+ENTER. If you prefer to have one formula, try the following instead...

=IF(ROWS($CK$5:CK5)<=SUMPRODUCT(--(COUNTIF($BW$5:$CF$18,$BW$1:$CF$1)=0)),INDEX($BW$1:$CF$1,SMALL(IF(COUNTIF($BW$5:$CF$18,$BW$1:$CF$1)=0,COLUMN($BW$1:$CF$1)-COLUMN($BW$1)+1),ROWS($CK$5:CK5))),"")

...confirmed with CONTROL+SHIFT+ENTER. Note that having it in one formula is not as effficient.

Hope this helps!

 

[Brew]

Thanks Domenic, that works perfectly!!!