Excel help please - excluding data

[Kinnon Bell]

Hi All,

I'm a complete excel novice but quickly learning thanks to google. However, one thing I cannot find is the solution for is extracting data by excluding certain numbers.

I have just learnt how to filter data via Advanced filter and was able to get what I want but now I need to do the opposite of what I just did and am needing to exclude about 250 items out of about 87,000.

What I've read on google people are saying it's not possible but tell me it is not true! I really really don't want to have to delete it one by one.

Any help would be greatly appreciated!

 

[Ski-bum]

Can you try just filtering for all the data you want to include, then copy all that data into a new worksheet - thus the new worksheet won't have the data you didn't want.

 

[devank]

Step 1 :
List all the numbers you don't want as a table.
Do a VLOOKUP.

Step 2:
Copy and paste the original list and the lookedup list (paste as value).

Step 3:
Sort by the results col (looked up value).

Delete the top part and keep the rest.

See the attached excel for quick & dirty way.

 

[Kinnon Bell]

Can you try just filtering for all the data you want to include, then copy all that data into a new worksheet - thus the new worksheet won't have the data you didn't want.

I was thinking that but the only way I can see to do it is to do it one by one which will be my last option if I can't figure out how to do it by bulk.

Step 1 :
List all the numbers you don't want as a table.
Do a VLOOKUP.

Step 2:
Copy and paste the original list and the lookedup list (paste as value).

Step 3:
Sort by the results col (looked up value).

Delete the top part and keep the rest.

See the attached excel for quick & dirty way.

Thanks, I'll have a play with that.

 

[Joel]

I'm not sure which Excel versions this applies to but my guess is Excel 2007 onwards.

My assumption is your have a column of numbers with the first row being the name of the column (e.g. Row 1 = Price, Row 2 = $1.33, Row 3 = $1.54 etc). You could try a filter and then filter for numbers in between two limits?

1. Select the cell on the top of column of numbers (i.e. Row 1 = Price)
2. In the ribbon menu at the top, Left-click on Data
3. On the new menu which appears below, Left-click on Filter
4. The cell on the top of the column of numbers should now have a little downward triangle, Left-click on it
5. Hover mouse pointer over Number filters
6. Left-click on Between
7. Set limits and Left-click on OK

It should then display only the rows which have a price that is in-between the limits you have set (Note: the other rows are still there but they are hidden). You can select them and copy-paste them onto a new sheet for further analysis.