I have an interesting problem. I have one workbook with two sheets.
The first sheet contains columns A thru U, the about 1600 rows. It is
a list of employees and information about them pertaining to their
job, so, column K has a job code number, 4 digits. Sheet 2 is a list
of 'key' job codes, one column, 68 rows. What I'd like to do, maybe
with VBA is delete the entire row from the first sheet if it's job
code number from column K is not on the list from the second sheet.
So what remains is a list of employess that have those key jobs.

Any ideas?

Hi
use VLOOKUP if it is a once off..
Assuming your data starts in row 2, on the first sheet in cell V2 type
=Vlookup(
1. In this formula, click on the first job code number in cell K2,
then type a comma
2. go to sheet 2 and select the column of 68 job codes with the mouse.
If this is Sheet2!A2:A69 for example, replace it with Sheet2!$A$2:$A
$69. Type a comma
3. Type 1, then a comma
4. Type False and finish the formula with a bracket so you have
something like
=VLOOKUP(K2, Sheet2!$A$2:$A$69,1,False)

press return. Copy this formula down the rest of the rows. Any code on
sheet 1 which is not in sheet 2 will have an N/A message in column U.
Filter sheet 1 for the N/A's and delete the rows

regards
Paul

On May 11, 3:12 pm, wrote:
I have an interesting problem. I have one workbook with two sheets.
The first sheet contains columns A thru U, the about 1600 rows. It is
a list of employees and information about them pertaining to their
job, so, column K has a job code number, 4 digits. Sheet 2 is a list
of 'key' job codes, one column, 68 rows. What I'd like to do, maybe
with VBA is delete the entire row from the first sheet if it's job
code number from column K is not on the list from the second sheet.
So what remains is a list of employess that have those key jobs.

Any ideas?

On May 11, 10:08 am, wrote:
Hi
use VLOOKUP if it is a once off..
Assuming your data starts in row 2, on the first sheet in cell V2 type
=Vlookup(
1. In this formula, click on the first job code number in cell K2,
then type a comma
2. go to sheet 2 and select the column of 68 job codes with the mouse.
If this is Sheet2!A2:A69 for example, replace it with Sheet2!$A$2:$A
$69. Type a comma
3. Type 1, then a comma
4. Type False and finish the formula with a bracket so you have
something like
=VLOOKUP(K2, Sheet2!$A$2:$A$69,1,False)

press return. Copy this formula down the rest of the rows. Any code on
sheet 1 which is not in sheet 2 will have an N/A message in column U.
Filter sheet 1 for the N/A's and delete the rows

regards
Paul

On May 11, 3:12 pm, wrote:



I have an interesting problem. I have one workbook with two sheets.
The first sheet contains columns A thru U, the about 1600 rows. It is
a list of employees and information about them pertaining to their
job, so, column K has a job code number, 4 digits. Sheet 2 is a list
of 'key' job codes, one column, 68 rows. What I'd like to do, maybe
with VBA is delete the entire row from the first sheet if it's job
code number from column K is not on the list from the second sheet.
So what remains is a list of employess that have those key jobs.

Any ideas?- Hide quoted text -

- Show quoted text -

Thanks, that's exactly what I ended up doing. I'll need to repeat
this once a quarter, wasn't too bad...it was all the formating
afterwards that seemed to be the most timely..! Thanks for your
thoughts!!