Formula to list missing values

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,403
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,820
Messages
5,574,509
Members
412,599
Latest member
Schu94
Top