AbdulkareemAlhassni

New Member
Joined
Nov 16, 2018
Messages
41
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 ?
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
697
Office Version
  1. 365
Platform
  1. Windows
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

ABCDEFG
1Activity CodeDateDateActivity CodeDate
2010114-Mar-19>=1-Jan-2019010114-Mar-19
3020113-Jan-19020113-Jan-19
4020301-Mar-19020301-Mar-19
5020301-Jan-19020301-Jan-19
6020304-Jan-18
7034003-Feb-18
8034401-Jan-17

<tbody>
</tbody>
Sheet1


<tbody>
</tbody>
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Nov 16, 2018
Messages
41

ADVERTISEMENT

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*
 
Last edited by a moderator:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Nov 16, 2018
Messages
41

ADVERTISEMENT

Re: A challenge in Advanced Filter


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.
 
Last edited by a moderator:

AbdulkareemAlhassni

New Member
Joined
Nov 16, 2018
Messages
41
Re: A challenge in Advanced Filter

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

First method:

ABCDE
1Activity CodeDateActivity CodeDate
210114-mar-19101>=01-01-2019
320113-ene-19201>=01-01-2019
420301-mar-19203>=01-01-2019
520301-ene-19340>=01-01-2019
620304-ene-19101*2019*
734003-feb-18201*2019*
834401-ene-19203*2019*
934001-mar-19340*2019*
1020113-ene-18*2019*
1120301-mar-18>=01-01-2019
12201year 2019 some text
13203some string mar 2019
14203data mar 2018 data
15203data mar 2020 data

<tbody>
</tbody>



Second Method:



ABCDEF
1Activity CodeDateActivity CodeDate
210114-mar-19101>=01-01-2019=B2>=01-01-2019
320113-ene-19201>=01-01-2019=B2=2019
420301-mar-19203>=01-01-2019
520301-ene-19340>=01-01-2019
620304-ene-19101*2019*
734003-feb-18201*2019*
834401-ene-19203*2019*
934001-mar-19340*2019*
1020113-ene-18
1120301-mar-18
12201year 2019 some text
13203some string mar 2019
14203data mar 2018 data
15203data mar 2020 data

<tbody>
</tbody>


Dont know which one is better ?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Nov 16, 2018
Messages
41
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:

ACDEFGH
1Activity CodeSub-ActivitySub-Sub-ActivityDateSub-Sub-ActivityDate
2101101-1John14-mar-19John>=01-01-2019
3201201-1Alpha13-ene-19Alpha>=01-01-2019
4203203-1Rock01-mar-19Micky>=01-01-2019
5203203-2Rock01-ene-19Sugar>=01-01-2019
6203203-3Micky04-ene-19
7340340-1Sugar03-feb-18
8340340-2Sugar01-ene-19
9340340-3Sweet01-mar-19
10201201-2Beta13-ene-18
11203203-4Micky01-mar-18

<tbody>
</tbody>


After the filter:

>=01-01-2019
7340340-1Sugar03-feb-18Sugar>=01-01-2019
8340340-2Sugar01-ene-19
11203203-4Micky01-mar-18

<tbody>
</tbody>

Hope you got what i need. :)
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,108,950
Messages
5,525,827
Members
409,665
Latest member
Jessyi

This Week's Hot Topics

Top