Sorting cells with two criteria

[PandaKing66]

Hello,
I have a program that queries large amounts of data from a server depending on what dates the user inputs. I have to plot this data from largest to smallest comparing it to a percentage which is in the column the left. The data returned has a variable number of points so the percentage column is created using a series from 100/n to 100. This creates a duration curve telling us for how long the queried data is above a certain point.

Where I'm having trouble is sometimes the query returns "No Data" instead of a value. With the sort I'm using (descending) all of these cells are placed at the top of the column. My question is how do I get the "No Data" cells to the bottom but still have the other cell sorted from largest to smallest?

My sort function is the following:
Range(Column & "4:" & Column & (Size + 3)).sort key1:=Range(Column & "4:" & Column & (Size + 3)), _
order1:=xlDescending, Header:=xlNo

Underlined = variable that is passed into the sub

 

[Domenic]

Via code, first create a temporary column where you assign each cell either 1 or 2. For cells that correspond to non-"No Data" cells, enter 2. For all others, enter 1. Then sort your data, including your temporary column. Sort first by the temporary column, in ascending order, and then by the target column, in descending order. Then, delete the temporary column.

Hope this helps!

 

[PandaKing66]

Via code, first create a temporary column where you assign each cell either 1 or 2. For cells that correspond to non-"No Data" cells, enter 2. For all others, enter 1. Then sort your data, including your temporary column. Sort first by the temporary column, in ascending order, and then by the target column, in descending order. Then, delete the temporary column.

Hope this helps!

Ah, yes that would work. Can you show me some sample code for the sort portion though? I'm having some difficulty understanding the syntax for the .sort method

 

[Domenic]

Sure, for simplicity, let's assume that Column A contains your original values to be sorted, and Column D contains the temporary column, try...

Range("A1:D100").Sort key1:=Range("D1"), order1:=xlAscending, key2:=Range("A1"), order2:=xlDescending, Header:=xlNo

Hope this helps!

 

[PandaKing66]

Sure, for simplicity, let's assume that Column A contains your original values to be sorted, and Column D contains the temporary column, try...

Range("A1:D100").Sort key1:=Range("D1"), order1:=xlAscending, key2:=Range("A1"), order2:=xlDescending, Header:=xlNo

Hope this helps!

That works nicely, but it does take a long time to run through. There are times when there are thousands of cells of data being returned (Even tens of thousands). I am realizing that this method will actually make this macro take a very long time to run and I do not know if it is worth it.

 

[Domenic]

I tested it on a sheet that contained 1,000,000 rows and 10 columns of data. It only took 4 or 5 seconds to sort it. Try commenting out that line of code so that you can run your code without it and see if in fact sorting is the culprit.

 

[PandaKing66]

I tested it on a sheet that contained 1,000,000 rows and 10 columns of data. It only took 4 or 5 seconds to sort it. Try commenting out that line of code so that you can run your code without it and see if in fact sorting is the culprit.

Thank you for the help, but I actually found a different solution through another site. Someone recommended I use the range().replace method to set all "No Data" cells to a 0.

As for your method: I'm thinking you're using a different method to go through each cell and check their values. I was using a for loop to individually check each cell and assign a value in the adjacent column depending on the contents. I'm guessing you came up with a way to do this for the entire column all at once.

And again, Thanks for the help!

 

[Domenic]

I'm rushing off, but here's a quick example...

Sub test()

    Dim LastRow As Long
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Range("D1").Value = "Temp Column"
    Range("D2:D" & LastRow).FormulaR1C1 = "=IF(RC1=""No Data"",2,1)"
    
    Range("A1:D" & LastRow).Sort key1:=Range("D1"), order1:=xlAscending, key2:=Range("A1"), order2:=xlDescending, Header:=xlYes
    
    Columns("D").Delete

End Sub

Does this help?