Forum Discussion
data filter
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.
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
have you tried pressing ctrl or alt this sometimes works for me
ctrl+mouse alt+mouse
simple answer
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
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-examples/ 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
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
