Data filters

[ellison]

Hi, some of my colleagues are really uneasy (aka downright nervous!) about using data filters. That’s whether applying them via the excel Options or using keyboard shortcuts.

The main issues are:

• how long it takes for them to set up and remove a filter
• that they may apply it to the wrong column

99% of the time, the files they’re working on only need 1 filter, and it would go on column c.

50% of the time, it would be for ‘column contains’ and 50% it would be for ‘column equals’

Column C contains the status column. At the moment it is a jumble of free text, part numbers (which are made up of alpha numeric characters), blank spaces and numbers. (This is what we are working on tidying up!).

They asked if it would be possible for something like this:

To set up a shortcut / hotkey / set of keyboard shortcuts which:

1. ‘filters column c’ for whatever search string you copy and paste into it
2. Option to choose either filter equals or filter contains
3. Removes the filter

I should probably add that the files they work on are pretty big i.e. 10-20 meg with about 15 fields and about 20K-ish rows.

(And apologies, I did post something like this a couple of days ago but with a clanger of a typo in the subject field with help needed with date (not data!) filtering).

If you needed any more details, please let me know

Huge thanks to anybody for taking a peek!

 

[rondeondo]

Hi Ellison,
10-20MB sounds quite big for 20k lines, does your column C contain very large amounts of data per cell?
Couple of things that may help, make sure the file ends at the end of your data, sometimes a formula or formatting may get applied to an entire sheet height of a column, >1 million rows, this will effect the file size and the filter operation. To check, press ctrl-end. This will take you to the last row and last column that is being stored in the file. If this is well beyond where your data finishes then select all the excess rows, ctrl-left arrow to go to the start of the row then shift-ctrl-up arrow to select from where you are to the last row of data. Then delete these rows. if of course you're not at the end of your actual data with the ctrl-end. Can do the same for excess columns.

Next thing, does your data contain a lot of formulas? this can effect the filter time as applying a filter will trigger a recalculation of formulas. If you do have formulas and need to keep them, turn off the auto calculate. If you don't need them, replace them with values.

Auto calculate: Formulas Ribbon --> Calculation section --> calculation options drop down.
Replace formulas with values --> Select all of your data, ctrl-A, copy and paste special - values. Paste special is found under the right click menu

Next, filters. Depending on your situation it may be beneficial to convert your data to a table, select all, ctrl-A then make table, Ctrl-T this will automatically add filters to the data.
If a table isn't what you want, you can just apply an auto filter, again select all you data with ctrl-A then go to the data ribbon --> sort & filter section and click on the filter button.

When you open the filter, with the down arrow box, there is a "Text Filters" option. Pick this and then you have a number of options including "Contains" this then opens another option box and you can put in your search term here. You can even put in two at a time and switch between contains, not contain and a few others.

Don't fear the data, save your messing around file as another file so you can come back to the original as needed.
Hope this helps

 

[Fluff]

Duplicate to: Date Filtering – Help needed with some nervous Excel users!

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.