data filter

[Michele Marco Schiavon]

Ciao,

I have an easy question, I hope you can help me.

I have a table with in column A a list of project codes(code ex is E-APT00200_15). I want to filter column A for a group of project codes. I could do it one by one, selecting everytime the button "add to existing list".

But, is there a way to copy and paste all the project codes at once? Could I apply the method to a table and a pivot table?

Other question, once I have filtered the column for the project codes I need (within 100 I've selected 30), is there a way to tell excel "show me what is not selected"? In a normal table I can highlight the selected ones and then filtered for "not colored", but in a pivot table apparently I cannot highligh only the selected onces (the once that i see) because excel highlight the whole list.

Thanks a lot for your help!!

Ciao

[#2]

Hi Michele,

Not sure i understand everything, but

1) Let assume you have a table with codes like this

and you filtered it somehow, let say only Code1 and Code3 are selected. When you do simple copy/paste of the selected filtered table Excel will paste only visible cells

2) To show unselected rows use helper column, add blank one to your table, enter any text ("Filtered") in first cell of this column and copy it down to table

After that clean filter on Code column and apply filter on blank cells to Helper column, you will see not filtered previously codes

Above is for Table.

[#3]

Hi Sergei,

thanks a lot for your reply. Unfortunatly it's not the correct one. :)

I try to explain better the question. Using your exemple, I want to filter every time for code1 and code3, but my codes are not so easy readable and I have more than 2. So I cannot scroll down in the filtered and just select what I want because it's time consuming and difficult to find the codes. At the moment I write "code1" in the search field and I filter. Then I write "code2" in search field ad flag "add to existing". In this way I have my filtered list. But again, once i have 20 codes it's a bit time consuming.

- The question is, is there a way to write in the search field more than one code at the same time (like all the codes separeted by the comma or something else to allow excel look for all the codes as I'm doing manually)?

-The other point is: working in a normal table I can use the helper column (or the colors as I'm doing now). But when I use a pivot table i cannot use the the "external" helper column (cause it's not linked to the raw when I unfilter). I tried to add a calculated field in the pivot and use the formula count to the "code column" (in this way I get a "1" for every line) but the formula apply to the whole list (so I have a "1" for all the lines once I unfiltered).

Sorry for the long post, I hope it's more clear now.

Really, thanks for your help.

Ciao

Michele

[#4]

Michele,

Okay, let start from first question. You may generate helper table with list of all your search criteria

When select your source table and in Ribbon select Data->Advance under filterig, it looks like

In Criteria range select your table with filters. If it'll be in form as above "table notation" expand with INDIRECT function

When Ok and you will receive filtered table

If you change your filters in table below just stay on source table and and Data->Advance->Ok to update the filtering

[#5]

Michele,

Back to the tables - finaly i found this article https://www.ablebits.com/office-addins-blog/2016/09/14/use-advanced-filter-excel-criteria-range-exam... which explains you much better than me how to work with multiply criteris.

Based on it for our sample how to show non-filtered items

let add to A17 any text and in A18 the formula

=COUNTIF(Table2[[#All],[Code]],A2)=0

when apply in Criteria range $A17:$A18, the result will be

[#6]

Ciao Sergei,

thanks a lot for your reply. It's working and I also discovered some new functionalities about the Advanced Filters.

thanks for your help.

Ciao

Michele

[#7]

have you tried pressing ctrl or alt this sometimes works for me

ctrl+mouse                        alt+mouse

simple answer