Extract values from a range that are missing from another

I've got two columns in Excel, "ROSTER" and "PRESENT", shown below:

column image

Is there a formula to achieve the "NOT HERE" column? I tried using VLOOKUP() and https://superuser.com/a/289653/135912 to no avail =(

Any help would be appreciated!

Thanks!

Kevin Hua

Posted 2012-05-31T03:16:23.647

Reputation: 272

How about this? http://superuser.com/questions/81498/excel-find-items-in-one-column-that-are-not-in-another-column

– bfhd – 2012-05-31T03:22:52.353

That works, but I can't have empty spaces http://i.imgur.com/ETpb1.png

– Kevin Hua – 2012-05-31T03:43:21.217

check out the solution from jeeped in that link - that won't give you empty spaces - it assumes Excel 2007 or later, which version do you have? – barry houdini – 2012-05-31T13:33:20.250

Answers

There is no built-in function that can singlehandedly do this task.

You can try this array formula in the "Not Here" column (MS Excel 2007+)

=IFERROR(INDEX(roster,SMALL(IF(COUNTIF(present,roster)=0,ROW()-1,""),ROW()-1),1),"")

Where (in my example)
roster is a Named Range that refers to $A$2:$A$21
present is a Named Range that refers to $B$2:$B$21

To enter the formula, select the cells in the Not Here column (in my case it's C2 down to C21), type the formula and then press Ctrl+Shift+Enter

enter image description here

Ellesa

Posted 2012-05-31T03:16:23.647

Reputation: 9 729

This may be a bit overkill, but it works. Hopefully you don't mind having an intermediate 'Not Here' column with spaces, before arriving at the end result (Not Here 2).

Picture of working solution

Behind the scenes:

Named ranges in use:

Roster: (B3:B19)
Present: (C3:C19)
NotHere: (F3:F19)

Array formula entered into range (D3:D19)...

{=IF(ISERROR(MATCH(Roster,Present,0)),Roster,"")}

Array formulae entered into cells (E3:E19)...

{=IFERROR(INDEX(NotHere,SMALL(IF(FREQUENCY(IF(NotHere<>"",MATCH(ROW(NotHere),ROW(NotHere)),""),MATCH(ROW(NotHere),ROW(NotHere)))>0,MATCH(ROW(NotHere),ROW(NotHere)),""),ROW(A1)),COLUMN(A1)),"")}

{=IFERROR(INDEX(NotHere,SMALL(IF(FREQUENCY(IF(NotHere<>"",MATCH(ROW(NotHere),ROW(NotHere)),""),MATCH(ROW(NotHere),ROW(NotHere)))>0,MATCH(ROW(NotHere),ROW(NotHere)),""),ROW(A2)),COLUMN(A2)),"")}

etc...

Though this looks a long-winded solution, it will work no matter where the table is placed within the worksheet. It also removes #num errors in Excel 2007, should you be using that version.

Iakovosian

Posted 2012-05-31T03:16:23.647

Reputation: 519