Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 http://superuser.com/a/289653/135912 to no avail =(

Any help would be appreciated!

Thanks!

share|improve this question
    
How about this? superuser.com/questions/81498/… –  bfhd May 31 '12 at 3:22
    
That works, but I can't have empty spaces i.imgur.com/ETpb1.png –  Kevin Hua May 31 '12 at 3:43
    
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 May 31 '12 at 13:33

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.