Excel Geniuses?

[GRitchie]

Hello,

Here we go...

 

We're running a survey via which users will enter their username so that's a unique key to each survey

 

We then want to compare this with a list of usernames from AD and find out who HASN'T filled in the survey.

 

 

The problem we are facing is we have no idea how... I'd presume using VLOOKUP and CONCATENATE?

But also, AD exports the list and they look like this:

[TABLE=width: 233]

[TR]

[TD=width: 233]%USERNAME%,User,,[/TD]

[/TR]

[/TABLE]

 

 

Anyone any ideas...?

[LosOjos]

Yeah you could do a vlookup, if the value was missing it'd return #N/A which you could filter down to find who's not done it (assuming everyone has spelt their username correctly of course)

 

To pull the %USERNAME% part of the text, use left and search. e.g. if you're looking at the value in cell A1:

 

=LEFT(A1,SEARCH(",",A1)-1)

 

Alternatively, use Excel's 'Text to Columns' routine to separate the values by splitting them by comma.

 

All in all, say Sheet1 had survey usernames in rows 1 to 50, your formula for cell A2 on your AD username sheet would be:

 

=VLOOKUP(LEFT(A1,SEARCH(",",A1)-1),'Sheet1'!$A$1:$A$50,1,FALSE)

 

If it returns the username, it found it (i.e. it's been completed) if it returns an error it didn't (i.e. not completed)

 

And if you wanted to tidy it all up a bit and return TRUE if it's entered and FALSE if not, wrap it all in an ISERROR (and NOT, to reverse it) i.e.:

 

=NOT(ISERROR(VLOOKUP(LEFT(A1,SEARCH(",",A1)-1),'Sheet1'!$A$1:$A$50,1,FALSE)))

Edited November 19, 2014 by LosOjos