Hi there
I want to filter data in two steps. First, I want to extract certain data based on specific criteria and paste this on a separate sheet. Then, I also want to paste the data not specified by the criteria (original data excluding the values filtered in the first step) on another sheet.
I managed to do the first step but I don't know how to specify my criteria to get all the data except those filtered in the first step.
I have the following example:
<tbody>
</tbody>
First, I extracted Cost Centres 1 and 3 with Cost Elements starting with "Wages". The criteria is in A1:B3 and the results underneath.
<tbody>
</tbody>
For the next step, I now want all the data except the values above, e.g.
<tbody>
</tbody>
but filtering with <>1, <>3 and <>Wages* only gives me the following:
<tbody>
</tbody>
...therefore excluding all Cost Elements starting with "Wages". But for the second step I still want to see "Wages" that are not in Cost Centre 1 or 3, for instance, I don't want it to throw out Cost Centres 2 and 4 with "Wages*".
Is there a way to specify that the filter should show everything but the values in the criteria? Otherwise, how should I set up the criteria to get the desired results?
I want to filter data in two steps. First, I want to extract certain data based on specific criteria and paste this on a separate sheet. Then, I also want to paste the data not specified by the criteria (original data excluding the values filtered in the first step) on another sheet.
I managed to do the first step but I don't know how to specify my criteria to get all the data except those filtered in the first step.
I have the following example:
<tbody> </tbody> |
<tbody> </tbody> | ||
1 |
<tbody> </tbody> | ||
2 |
<tbody> </tbody> | ||
1 |
<tbody> </tbody> | ||
3 |
<tbody> </tbody> | ||
2 |
<tbody> </tbody> | ||
4 |
<tbody> </tbody> | ||
4 |
<tbody> </tbody> | ||
3 |
<tbody> </tbody> | ||
1 |
<tbody> </tbody> | ||
3 |
<tbody> </tbody> |
<tbody>
</tbody>
First, I extracted Cost Centres 1 and 3 with Cost Elements starting with "Wages". The criteria is in A1:B3 and the results underneath.
<tbody> </tbody> |
<tbody> </tbody> | ||
1 | Wages* | ||
3 | Wages* | ||
<tbody> </tbody> |
<tbody> </tbody> | ||
1 |
<tbody> </tbody> | ||
1 |
<tbody> </tbody> | ||
3 |
<tbody> </tbody> | ||
3 |
<tbody> </tbody> |
<tbody>
</tbody>
For the next step, I now want all the data except the values above, e.g.
Cost Center | Cost element name | |
1 |
<tbody> </tbody> | |
2 |
<tbody> </tbody> | |
2 |
<tbody> </tbody> | |
3 |
<tbody> </tbody> | |
4 |
<tbody> </tbody> | |
4 |
<tbody> </tbody> |
<tbody>
</tbody>
but filtering with <>1, <>3 and <>Wages* only gives me the following:
Cost Center | Cost element name | |
2 |
<tbody> </tbody> | |
4 |
<tbody> </tbody> | |
1 |
<tbody> </tbody> | |
3 |
<tbody> </tbody> |
<tbody>
</tbody>
...therefore excluding all Cost Elements starting with "Wages". But for the second step I still want to see "Wages" that are not in Cost Centre 1 or 3, for instance, I don't want it to throw out Cost Centres 2 and 4 with "Wages*".
Is there a way to specify that the filter should show everything but the values in the criteria? Otherwise, how should I set up the criteria to get the desired results?