How to keep columns of data together when sorting on one?

[rizzo93]

I keep a daily total of counts for several departments for each day. I sort on these counts from largest to smallest in the second column.

I need to keep these counts together with their corresponding departments as the sort order changes day to day (based on the second column).

For example, I've highlighted Finance to show where it changes in the sort order.

First day

Second day

Third day

On 5/28 and 5/29, Finance has the same number of counts and remains in slot 4. But on 6/1, it moves down to slot 5 with a count of 5. I need to keep the other values in the date columns moving along with it (this goes for all departments, of course). I've been doing this manually, so a dynamic formula is what I'm looking for.

To help with this, I'm keeping a table with the history for each date/department.

What would the formula look like to keep the dates together and as I add to the columns to the dailies and rows for the history table?

 

[Logit]

Your table is sorting correctly.

 

[rizzo93]

Your table is sorting correctly.

Thanks for the reply, Logit. However, the only reason the data is sorting correctly is because I mentioned earlier that I'm having to do it manually. This is not an efficient method.

Do you have suggestions that would make it work with formulas?

 

[Logit]

This will sort your data :

Option Explicit

'##### Adjust range as required #####

Sub srtMe()
Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:D100" & LastRow).Sort Key1:=Range("A2:D100" & LastRow), _
       Order1:=xlDescending, Header:=xlYes
End Sub

As for an actual FORMULA ... I am not aware of a formula that will sort your data. That doesn't mean there isn't one ... I've just
never heard of one.

 

[rizzo93]

This will sort your data :

Option Explicit

'##### Adjust range as required #####

Sub srtMe()
Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:D100" & LastRow).Sort Key1:=Range("A2:D100" & LastRow), _
       Order1:=xlDescending, Header:=xlYes
End Sub

As for an actual FORMULA ... I am not aware of a formula that will sort your data. That doesn't mean there isn't one ... I've just
never heard of one.

Thank you, Logit.

 

[Logit]

You are welcome. Glad to help.

Cheers !