Advanced Filter

[AbdulkareemAlhassni]

Dear all,

i have a table as follows

Activity Code Date
0101 3/14/2019
0201 1/13/2019
0203 3/1/2019
0203 1-1-2019
0203 1-4-2018
0340 2/3/2018
0344 1/1/2017

..................
When i apply the following filter

i get the date for our Purchase starting from 2019 and forward:

Date
*2019*
>=1/1/2019


----------------

The actual filter i want is i want to filter the Date with respect to specific Activity Codes, so

what i actually runs as a filter ( which is what i want )

..................
When i apply the following filter


Activity Code Date
0101 *2019*
0201 >=1/1/2019
0203
0203
0203
0340
0344


The problem is here i keep getting dates before 2019 such as 2018. because the filter is reading all " Activity Code" and returns all of them and " Keeps ignoring the "Date Filter" somehow,

how can i fix this ?

 

[mse330]

Re: A challenge in Advanced Filter

Hello Abdulkareem,

If you are using excel's "Advanced Filter", then just make sure the header name of the Criteria range cell is the exact same as the header in your original data. So, looking at the below sample, column D headers = "Date" & below that I have specified the criteria of ">=1-Jan-2019" & my advanced filter output is in range F1:G5

Another note is the date formats in your provided sample where it has / sometimes & - other times which might actually be a text cell

If you need to establish better understanding of the advanced filter, you can visit the below link :
https://trumpexcel.com/excel-advanced-filter/

Post back if you have further questions

	A	B	C	D	E	F	G
1	Activity Code	Date		Date		Activity Code	Date
2	0101	14-Mar-19		>=1-Jan-2019		0101	14-Mar-19
3	0201	13-Jan-19				0201	13-Jan-19
4	0203	01-Mar-19				0203	01-Mar-19
5	0203	01-Jan-19				0203	01-Jan-19
6	0203	04-Jan-18
7	0340	03-Feb-18
8	0344	01-Jan-17

<tbody>
</tbody>

Sheet1​

<tbody>
</tbody>

 

[DanteAmor]

Re: A challenge in Advanced Filter

In column "E" you have to put the date condition for each record in column "D".


Before the filter:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:84.59px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:127.37px;" /><col style="width:137.82px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Activity Code</td><td >Date</td><td > </td><td >Activity Code</td><td >Date</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">101</td><td style="text-align:right; ">14-mar-19</td><td > </td><td style="text-align:right; ">101</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">201</td><td style="text-align:right; ">13-ene-19</td><td > </td><td style="text-align:right; ">201</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">203</td><td style="text-align:right; ">01-mar-19</td><td > </td><td style="text-align:right; ">203</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">203</td><td style="text-align:right; ">01-ene-19</td><td > </td><td style="text-align:right; ">340</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">203</td><td style="text-align:right; ">04-ene-19</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">340</td><td style="text-align:right; ">03-feb-18</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">344</td><td style="text-align:right; ">01-ene-19</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">340</td><td style="text-align:right; ">01-mar-19</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">201</td><td style="text-align:right; ">13-ene-18</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">203</td><td style="text-align:right; ">01-mar-18</td><td > </td><td > </td><td > </td></tr></table>

After the filter:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:84.59px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:127.37px;" /><col style="width:137.82px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Activity Code</td><td >Date</td><td > </td><td >Activity Code</td><td >Date</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">101</td><td style="text-align:right; ">14-mar-19</td><td > </td><td style="text-align:right; ">101</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">201</td><td style="text-align:right; ">13-ene-19</td><td > </td><td style="text-align:right; ">201</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">203</td><td style="text-align:right; ">01-mar-19</td><td > </td><td style="text-align:right; ">203</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">203</td><td style="text-align:right; ">01-ene-19</td><td > </td><td style="text-align:right; ">340</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">203</td><td style="text-align:right; ">04-ene-19</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">340</td><td style="text-align:right; ">01-mar-19</td><td > </td><td > </td><td > </td></tr></table>

 

[AbdulkareemAlhassni]

Re: A challenge in Advanced Filter

Hello Abdulkareem,

What about the other date condition its a must for me !

 

[AbdulkareemAlhassni]

Re: A challenge in Advanced Filter

In column "E" you have to put the date condition for each record in column "D"...

I have date with text because of different date formatting, i need the other filter *2019*

 

[DanteAmor]

Re: A challenge in Advanced Filter

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:84.59px;" /><col style="width:153.03px;" /><col style="width:47.52px;" /><col style="width:127.37px;" /><col style="width:137.82px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Activity Code</td><td >Date</td><td > </td><td >Activity Code</td><td >Date</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">101</td><td style="text-align:right; ">14-mar-19</td><td > </td><td style="text-align:right; ">101</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">201</td><td style="text-align:right; ">13-ene-19</td><td > </td><td style="text-align:right; ">201</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">203</td><td style="text-align:right; ">01-mar-19</td><td > </td><td style="text-align:right; ">203</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">203</td><td style="text-align:right; ">01-ene-19</td><td > </td><td style="text-align:right; ">340</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">203</td><td style="text-align:right; ">04-ene-19</td><td > </td><td style="text-align:right; ">101</td><td >*2019*</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">340</td><td style="text-align:right; ">03-feb-18</td><td > </td><td style="text-align:right; ">201</td><td >*2019*</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">344</td><td style="text-align:right; ">01-ene-19</td><td > </td><td style="text-align:right; ">203</td><td >*2019*</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">340</td><td style="text-align:right; ">01-mar-19</td><td > </td><td style="text-align:right; ">340</td><td >*2019*</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">201</td><td style="text-align:right; ">13-ene-18</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">203</td><td style="text-align:right; ">01-mar-18</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">201</td><td >year 2019 some text</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">203</td><td >some string mar 2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">203</td><td >data mar 2018 data</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">203</td><td >data mar 2020 data</td><td > </td><td > </td><td > </td></tr></table>

 

[AbdulkareemAlhassni]

Re: A challenge in Advanced Filter

Try this

Great its working but i still have another issue,

How can i tell this filter to :

If there is any other Code other the ones specified with a Date >=1/1/2019 or has a text format of *2019* to include it ?

the above filter is only filtering with regards to the provided activities, but i have other activites within 2019 that i DONT want to exclude.

 

[AbdulkareemAlhassni]

Re: A challenge in Advanced Filter

I think i found two ways: i will test them now:

First method:

	A	B	C	D	E
1	Activity Code	Date		Activity Code	Date
2	101	14-mar-19		101	>=01-01-2019
3	201	13-ene-19		201	>=01-01-2019
4	203	01-mar-19		203	>=01-01-2019
5	203	01-ene-19		340	>=01-01-2019
6	203	04-ene-19		101	*2019*
7	340	03-feb-18		201	*2019*
8	344	01-ene-19		203	*2019*
9	340	01-mar-19		340	*2019*
10	201	13-ene-18			*2019*
11	203	01-mar-18			>=01-01-2019
12	201	year 2019 some text
13	203	some string mar 2019
14	203	data mar 2018 data
15	203	data mar 2020 data

<tbody>
</tbody>

Second Method:

	A	B	C	D	E	F
1	Activity Code	Date		Activity Code	Date
2	101	14-mar-19		101	>=01-01-2019	=B2>=01-01-2019
3	201	13-ene-19		201	>=01-01-2019	=B2=2019
4	203	01-mar-19		203	>=01-01-2019
5	203	01-ene-19		340	>=01-01-2019
6	203	04-ene-19		101	*2019*
7	340	03-feb-18		201	*2019*
8	344	01-ene-19		203	*2019*
9	340	01-mar-19		340	*2019*
10	201	13-ene-18
11	203	01-mar-18
12	201	year 2019 some text
13	203	some string mar 2019
14	203	data mar 2018 data
15	203	data mar 2020 data

<tbody>
</tbody>

Dont know which one is better ?

 

[DanteAmor]

Re: A challenge in Advanced Filter

If you put this

>=01-01-2019

*2019*

<colgroup><col></colgroup><tbody>
</tbody>

Then it is not necessary to put this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:127.37px;" /><col style="width:137.82px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Activity Code</td><td >Date</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">101</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">201</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">203</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">340</td><td >>=01-01-2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">101</td><td >*2019*</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">201</td><td >*2019*</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">203</td><td >*2019*</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">340</td><td >*2019*</td></tr></table>


Since all activities are in *2019* or greater than 01/01/2019

 

[AbdulkareemAlhassni]

Re: A challenge in Advanced Filter

Ok thank you you were right,

last question,

Assume i have another two columns, and the way am filtering (Main Source) using the Sub-Sub-Activity:

Filter is used to get its "Sub-Activity" values

and then i wanna run a different filter using "Sub-Activity" values on the (Main Source) again

Is there a more effcient way of doing it rather than

1.Filter Main source
2. Obtain Data
3. Filter Main source again "using obtain data"


Before the filter:

	A	C	D	E	F	G	H
1	Activity Code	Sub-Activity	Sub-Sub-Activity	Date		Sub-Sub-Activity	Date
2	101	101-1	John	14-mar-19		John	>=01-01-2019
3	201	201-1	Alpha	13-ene-19		Alpha	>=01-01-2019
4	203	203-1	Rock	01-mar-19		Micky	>=01-01-2019
5	203	203-2	Rock	01-ene-19		Sugar	>=01-01-2019
6	203	203-3	Micky	04-ene-19
7	340	340-1	Sugar	03-feb-18
8	340	340-2	Sugar	01-ene-19
9	340	340-3	Sweet	01-mar-19
10	201	201-2	Beta	13-ene-18
11	203	203-4	Micky	01-mar-18

<tbody>
</tbody>

After the filter:

>=01-01-2019
7	340	340-1	Sugar	03-feb-18		Sugar	>=01-01-2019
8	340	340-2	Sugar	01-ene-19
11	203	203-4	Micky	01-mar-18

<tbody>
</tbody>

Hope you got what i need.