Sorting with multiple criteria

[jungix]

I need to sort by a score in column A, but only if today verifies a condition involving 2 other dates in column B and C (actually it's a little more complex but I'll simplify to make it clearer).

My first idea was to use an empty column, let's say D, and put my conditions in there with IFs and ANDs to put 1 or 0 in D, and then sort all my data with a first criteria on D and then a second on A. That would work, but I was looking for a smarter way to do that, without having to use an extra column. How can I sort with a complex criteria (involving ANDs and TODAY())? Or is it not possible? Or maybe with an autofilter?

[Zack Barresse]

Hey jungix,

You can autofilter with only two criteria. They must be criteria that you can filter the entire column on. So you could use something like this criteria1:="=" & Date, you can also filter each subsequent column with two criteria as well. Does this help? If not, tell us how your data is structured, where your headers are, where your data starts and all of your criteria.

[jungix]

I've attached a sample. I would like to sample by column C but only if
AND(TODAY()<>date1,TODAY()+1<>date1,TODAY()+2<>date1,TODAY()<>date2,TODAY() <>date2+1).

I know I could compute this formula in another column, but this would be heavy.

[Zack Barresse]

Let me make sure I understand this logic first...

Five conditions, all must be met
1) Date in col A must NOT equal TODAY()
2) Date in col A must NOT equal TODAY() plus 1
3) Date in col A must NOT equal TODAY() plus 2
4) Date in col B must NOT equal TODAY()
5) Date in col B plus 1 must NOT equal TODAY()
Also, they must be dates, not strings as you have in some cells

Is this correct?

[jungix]

You understood my 5 conditions well.

However, and I forgot to precise, if there is no date in a cell, then the row must not be eliminated from the rankings, which complicates things a little bit, because if only one date is available, some of the criteria would still have to hold. If in column B there is not a date, conditions 1 to 3 must hold for the row to be taken into account in the rankings.

[Zack Barresse]

So the first three conditions can meet if there is NO date in col B? Also, are you wanting to delete these rows?

[jungix]

No I can't delete this rows as I will do this on a daily basis and the dates will change. I will actually sort by several criterias in several columns and copy paste the first 10 according to each criteria, but for some of them I have to add this conditions about the dates.

Maybe the simplest thing is to create a column with a boolean about this dates, but that wouldn't be very nice, especially if I have to use different criterias about the dates for different sorts (with respect to other columns), because I would have to create one column per possible criteria

[Zack Barresse]

Why one column per possible criteria? That's what OR statements are for.

And I don't see anything wrong with using a helper column. Sure use the criteria of AutoFilter if you can, but it's very lacking, so sometimes (a lot of times for me) we need to make room for our criteria with formula.

[jungix]

Sorry I wasn't clear.

The 5 criteria I gave you were for one other column (assume it's C as I told you).

But then I will have to sort everything by D with maybe only 3 criteria on the date, and then by E with 7, so for each column which need a criteria I may have to create a new column with booleans.

Thanks for your help I don't think it would be easier with an autofilter anyway.

[Zack Barresse]

You could also set this up as an AdvancedFilter, setting up your criteria in a new table, possibly on a new sheet (is what I'd probably do anyway).

[mdmackillop]

You could use a UDF to create a filter column based on your date restrictions and initially filter on this. A Custom Autofilter could then be applied to your Volumes data

[vba]
Sub Filters()
If Cells(1, 1) = "FilterColumn" Then GoTo Skipped
Columns("A:A").Insert Shift:=xlToRight
Skipped:
If Columns("A").AutoFilter = True Then Columns("A").AutoFilter
Cells(1, 1) = "FilterColumn"
Range(Cells(2, 2), Cells(2, 2).End(xlDown)).Offset(, -1).FormulaR1C1 = "=Crit(RC[1],RC[2])"
Columns("A").AutoFilter Field:=1, Criteria1:="1"
End Sub

Function Crit(Dt1 As String, Dt2 As String)
Dim Test1 As Long, Test2 As Long
Today = Int(Now())
If IsDate(Dt1) Then
Select Case DateValue(Dt1)
Case Today, Today + 1, Today + 2
Test1 = 0
Case Else
Test1 = 1
End Select
Else
Test1 = 1
End If

If IsDate(Dt2) Then
Select Case DateValue(Dt2)
Case Today, Today - 1
Test2 = 0
Case Else
Test2 = 1
End Select
Else
Test2 = 1
End If
If Test1 = 0 Or Test2 = 0 Then
Crit = 0
Else
Crit = 1
End If
End Function

[/vba]

[mdmackillop]

Note: The final logic of the Crit value may need to be amended. I'm not sure if I got that right!

[jungix]

Thank you mdmackillop. I tested it and Crit gives me the good value, even in the case when the 2 dates were not both filled.

However, I am still a noob in VBA, and I don't understand the utility of using autofilter here with the example you provide. I don't care about the rows being invisible or not. I don't see the difference with:

[VBA]
Cells.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
[/VBA]

I just recall you that I need to sort to get the top 10 that satisfy Crit, but I will always have at least 10 rows which satisfy Crit. Correct me if I'm wrong, but I have the feeling that the following would do the same without using autofilter. I thought of autofilter hoping that it would not require a new column, but since you do the same what is the advantage of autofilter compared to Sort?

[VBA]
Cells.Sort Key1:=Range("A2"), Key2:=Range("D2"), _ Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
[/VBA]

Sorry if that sounds like a stupid question I'm not really familiar with autofilter.

[mdmackillop]

... but I will always have at least 10 rows which satisfy Crit.

By using the Autofilter on Crit, you can guarantee that your top 10 Volumes, or any other will satisfy Crit. If you're happy to do a manual check then it is certainly unnecessary.
I'm not clear what you're doing with the data, but if you're copying it elsewhere, then the FilterColumn can be deleted upon completion of the task.