Excel Filter 1

[jasonmorovich]

I have a Excel 2003 file that has 8 worksheets with about 45,000 lines of data in each worksheet. The data is an employee list with hours worked, employee number, total amount paid, last name, and first name. There is about 1000 employees in this list and i need to filter it out to get only the 150 I need data on. I have the employee numbers that I need. Currently I am doing the Auto-Filter on the employee number column, selecting the ONE employee number, and cutting and pasting it to another worksheet. I would have to do this 150 times for all 8 worksheets!! There has to be an easier way to filter all of this data only once to get the employee numbers I need.
Does anyone know of an advanced filter or macro that would allow me to do this or a software add-in?

I'm an Excel Newbie and any help with this would be greatly appreciated!

Thanks again...

 

[xlbo]

Put your 150 employee numbers in a seperate sheet

USe VLOOKUP against every line in your data to return a TRUE / FALSE as to whether they are in your list or not:

=IF(ISNA(VLOOKUP(EmpNo,ListOf150,1,false)),"EXC","INC")

where EmpNo is a cell ref in your data containing employee number and ListOf150 is the range where you have placed your 150 employee numbers that you are interested in

Copy the formula and paste special>values

then simply filter on "INC"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jasonmorovich]

Geoff,
Thanks so much for the quick response! I understand part of your reply, however i'm having trouble with other parts (again i'm new at this so please be patient with me). Here's what i think the sentence should look like according to your help:

=IF(ISNA(VLOOKUP(janfeb!G2:G40891,empnum!A1:A150,1,false)),"EXC","INC")

Where janfeb is the name of the worksheet with G2-G40891 the range where all the employee numbers are located and empnum is the worksheet i set up with the employee numbers i need and A1-A150 are those employee numbers.

If that is correct here are the next questios I have.
1) Where do i copy and paste this formula to?
2) What is EXC in this formula?
3) What is INC in the formula and how do i sort on it?

Again, thanks for being patient with me.

 

[xlbo]

nearly there - you need to add this as a formula to each line of data so:

=IF(ISNA(VLOOKUP(G2,empnum!$A$1:$A$150,1,false)),"EXC","INC")

would be your formula - should be entered in a new column in row 2 of one of your sets of data

You then need to copy it down to the bottom of your dataset to return an "INC" or EXC" result for each line. This may take a short while to calculate

Once you have done this, copy and paste special>Values as you don't need to keep the formulae live and they will slow down your sheet if left.

You should then have a list of INC / EXC for each row of data. The INC results are the ones that exist in your list of employees. Simply filter for INC and copy (rather than cut) the whole set of data to a new sheet.

Repeat for each sheet of data

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Welshbird]

(You may need to use ctrl + g, then special, then visible cells only to make sure you don't get the rows you have filtered out. I can't remember if you do though, and I don't ahve Excel on my mac at home to test!)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[jasonmorovich]

Geoff,
Thanks for clearing that up. So I understand that will work for the one row I put that in (giving me the excluded or included statement tried it and it worked), but are you saying that i need to place this formula in all 40,000 rows of data i have? If so then i would have to change the G2 part as well (If I leave the $G$2:$G$40891 I get the correct response as well without having to change the G2 value to the appropriate column). That seems like a ton of work. If I'm totally missing the point then by all means tell me what a jackass I am.

And again, thanks for your continued help with this.

 

[jasonmorovich]

Geoff,
Disregard the changing of the G2. I forgot that the spreadsheet will automatically change that on it's own (SORRY!!). So that all works then the only questions that's left is: Do I have to copy this into all 40,800 rows?
Thanks...

 

[jasonmorovich]

Geoff,
Forget EVERYTHING I asked. I figured out the copy and paste in the whole column. It did exactly what i needed it to do!!

THANKS A MILLION!! I appreciate all your help...