RE : filtering problem

[Seeker]

Please help in advicing a way to do the extraction from following table?
Record No. Status Validation
1.1 add gtc
1.2 modify gtc
1.3 cancel gtc
2.1 add time less than now
2.2 modify time less than now
3.1 add gtc
3.2 modify gtc
4.1 add time greater than now
need to extract data in exact match all 3 criterias of:
1) last record with same figure before decimal, output are 1.3, 2.2, 3.2
2) <> cancel
3) validation column contains a mix of text and date/time value, output are
all gtc and time greater than now()
Advanced filter function can easily meet the 2) <> cancel, but can't figure
out how can I filter 1) & 3)
Expected final result should be record no. 3.2 & 4.1
I would like to filter & copy result to a new sheet by using macro instead
of asking users to proceed steps from manu bar.
Thanks in advance.

 

[Hong Quach]

Hi Seeker,

There are many way to do this. One not so quick but straight forward way to
filter your data base on your criteria is to create one column for each
filter to display the data when it satisfy the condition and display blank
otherwise. Finally, use the Auto filter to filter nonblank for each of the
added column. See the sample below.

Here I assume that each Record No. will be increase from X.1 to X.9 by 0.1
and not X.11 or X.1.1 (note: X.1 = X.10). When X.9 is reach, the next record
number is to be X+1.1. Therefore, 2.9 is followed by 3.1 so that the
different is 0.2. For this to work, the 1st column must be sorted descending
for this to work, which lead to another assumption that the last cell will
always get consider. For each filter, I use the "IF" function to do the
filtering.

Filter1
=IF(A3="",A2,IF(A3-A2>0.1,A2,""))

Filter2
=IF(B2<>"cancel",B2,"")

Filter3
=IF(C2>NOW(),C2,"")

(I added more record to demonstrate the change from 2.9 to 3.1)
RecNum Status Validation Filter1 Filter2 Filter3
1.1 add gtc add gtc
1.2 modify gtc modify gtc
1.3 cancel gtc 1.3 gtc
2.1 add 1/30/2009 add
2.2 modify 1/31/2009 modify
2.3 modify 2/1/2009 modify
2.4 modify 2/2/2009 modify
2.5 modify 2/3/2009 modify
2.6 modify 2/4/2009 modify
2.7 modify 2/5/2009 modify
2.8 modify 2/6/2009 modify
2.9 modify 2/7/2009 2.9 modify
3.1 add gtc add gtc
3.2 modify gtc 3.2 modify gtc
4.1 add 4/1/2009 4.1 add 4/1/2009

Result of filtering for Nonblank for Filter1, Filter2, Filter2
RecNum Status Validation Filter1 Filter2 Filter3
3.2 modify gtc 3.2 modify gtc
4.1 add 4/1/2009 4.1 add 4/1/2009

Now to convert this solution to a macro is simple. All left to do is to
fill the Filter columns with the "=IF(.....)" formula and apply nonblank
filter to them. You can use Copy and paste by value if you want the data to
stay and not change when copy and paste to other area.

Hong Quach

 

[Seeker]

Hi Hong,
Tks for your reply and I did adopted your method. However, I combimed 3
filter column into one "If" to displace the validation of data and make use
of the simple filter function in extracting those valid data. It works fine
now. Tks again