Comparing Text

B

beginner here

Everybody has been so helpful here, I was hoping not to have to bother
everybody with another question, but it was not to be.

My problem now is that I have a list of all workers, now some maybe gone for
whatever reason, which would be another list. What I am trying to do is get
a list of available workers for that day, to hand out to the supervisor.

Workers:
Dave
Tom
Steve
Paul
Tim
Ben
Edward

Workers Not available that day:
Steve
Tim
Edward

Available Workers
Dave
Tom
Paul
Tim
Ben

Now I would like to be able do this in a formula. I have tried the match
function, also have tried using the if statement, but nothing is working.

Any help would be appreciated.
 
R

Rick Rothstein \(MVP - VB\)

One possibility could be this...

Assuming your Workers are listed in Column A and the not available workers
are listed in Column B, both starting in Row 2 (assuming Row 1 is a header
row)... then place this formula

=IF(ISNA(VLOOKUP(A2,B$2:B$100,1,)),A2,"")

in Column C and copy down to the last row occupied in Column A... Column C
will show the list of available workers.

Rick
 
H

Harlan Grove

...
....
My problem now is that I have a list of all workers, now some maybe gone for
whatever reason, which would be another list. What I am trying to do is get
a list of available workers for that day, to hand out to the supervisor.

Workers:
Dave
Tom
Steve
Paul
Tim
Ben
Edward
Click to expand...

Name this list Everyone. I'll assume the top-left cell is C3.
Workers Not available that day:
Steve
Tim
Edward
Click to expand...

Name this list Absent
Available Workers
Dave
Tom
Paul
Tim
Ben
Click to expand...
....

In the column to the right of the Everyone list (col D given my assumption
above) enter these formulas.

D3:
=COUNTIF(Absent,C3)

Fill D3 down into D4:D9. The formulas will return 1 for absent employees, 0
for present employees. Select C2:D9, run the menu command Data > Filter >
AutoFilter. This will put drop-down list buttons in C2 and D2. Click the one
in D2, and select 0. This will produce a filtered list of just the present
employees, which you could print or copy and paste somewhere else.
 
B

beginner here

Rick,

Rick thanks for the formula, unfortunately when I copied the formula down in
Col C, after the first name in row two, every cell after that is left blank.

Steve
 
R

Rick Rothstein \(MVP - VB\)

Rick thanks for the formula, unfortunately when I copied the formula down
in
Col C, after the first name in row two, every cell after that is left
blank.
Click to expand...

You placed the workers names in A2:A8 (for the list that you posted), right?
You placed the formula in C2 and copied it down to C8, right? With nothing
in Column B, the entire list from Column A should be repeated in Column C.
As you type names in B2 through B8, those names should be removed from the
listing in Column C. This is not happening for you? I just repeated the
procedure I outlined above and it works fine here in Excel 2003.

Rick
 
B

beginner here

Yes, that is correct. When I type a name in col B, everything goes blank or
empty after the first name in the second row.

I just had a thought you said you tried it in 2003, so I went and checked
and here at work were are still on 2002.

Steve
 
B

beginner here

Rick,

I just found my problem, I went back and went character by character,
comparing your formula and the one that I typed, and I found one typo. Once
I corrected that, it worked just fine.

Thanks for your patience.

Steve
 
S

Sandy Mann

If you are reading in the Newsgroups then you can copy a formula, activate
XL, select the required cell, press the f2 key to enter Edit mode and then
paste the formula into the cell.

If you are reading in Google then you sometimes get an extra character,
usually a hyphen if I remember rightly, which you have to delete manually.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
Top