How to reverse the order of just one column in excel/google sheets?

user_11776488 0
Viewed 101

I have a table that looks like this:

Date   Home    Away
4/12   NY      TEX
4/12   TOR     ARI
4/12   LA      CIN
4/13   PHI     MIN
4/13   UTA     DEN
4/13   NAS     WYO

When I sort by Date either A to Z or Z to A, the Home and Away teams stay in the same order. How can I make the Home and Away columns reverse their current order? Meaning the result would look like this:

Date   Home    Away
4/12   LA      CIN
4/12   TOR     ARI
4/12   NY      TEX
4/13   NAS     WYO
4/13   UTA     DEN
4/13   PHI     MIN

I don't even mind if the date column gets sorted differently, I just need the Home and Away columns to reverse their order by Date. Thank you in Advance!

office365 excel-formula excel
3 Answers

Could also try sorting on row descending:

=SORTBY(A2:C7,A2:A7,1,ROW(A2:A7),-1)

enter image description here

=VSTACK(A1:C1,SORTBY(A2:C7,A2:A7,1,ROW(A2:A7),-1))

(copied from @JvdV) to include headers.

or even

=VSTACK(A1:C1,HSTACK(TEXT(A2:A7,"DD-MMM"),SORTBY(B2:C7,A2:A7,1,ROW(A2:A7),-1)))

to include the date formatting as well.

user_38949170

I figured it out. I had to create 3 more columns to do so... The first column created a value for each occurrence of date in the Date column. That looked like this:

=COUNTIF($A$1:A2,A2)

I then dragged the formula throughout the data. So now my table looked like this:

Date   Home    Away  Sort
4/12   NY      TEX   1
4/12   TOR     ARI   2
4/12   LA      CIN   3
4/13   PHI     MIN   1
4/13   UTA     DEN   2
4/13   NAS     WYO   3

The next column was created to reverse the order of the sort by date:

=MAXIFS(D:D,A:A,A2) - D2 + 1

Then dragged to the bottom and the new table looked like this:

Date   Home    Away  Sort RevSort
4/12   NY      TEX   1    3
4/12   TOR     ARI   2    2
4/12   LA      CIN   3    1
4/13   PHI     MIN   1    3
4/13   UTA     DEN   2    2
4/13   NAS     WYO   3    1

My third column Concatenated the Date and RevSort column giving me unique rows which I could then Sort the concatenated column and get my table sorted the way I wanted to.

user_117764880

Sort by a column other than date if you want the table sorted by something other than date.

If you want it sorted by home team A-Z then sort the home team column A-Z with all other columns selected.

user_159003880
Related