|
Comparing Text
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. |
Comparing Text
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 "beginner here" wrote in message ... 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. |
Comparing Text
"beginner here" wrote...
.... 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 Name this list Everyone. I'll assume the top-left cell is C3. Workers Not available that day: Steve Tim Edward Name this list Absent Available Workers Dave Tom Paul Tim Ben .... 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. |
Comparing Text
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 |
Comparing Text
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. 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 |
Comparing Text
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 "Rick Rothstein (MVP - VB)" wrote: 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. 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 |
Comparing Text
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 "Rick Rothstein (MVP - VB)" wrote: 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. 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 |
| All times are GMT +1. The time now is 12:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com