How to sort based on multiple rows

Hello, I'm sure there's a simple way to do this but unfortunately I can't find it. I want to sort rows based on dates that are on 2 different columns. I have attached an example with desired results, any advice would be greatly appreciated.

Many thanks!

Select the range that you will sort columns horizontally. In this case, select the B1:C23.
Click Home tab > arrow under Sort & Filter, and then click Sort Oldest to Newest, or Sort Newest to Oldest.

Thanks a lot for the quick reply. Sorry, my explanation was not clear at all.

What I would like to do is sort rows from the oldest to the newest date after a given day, but these dates are in 2 different columns.

Yes it's a typo, it should read June instead than July.

Thank you for taking the time to come up with a formula. I'll try to apply it later and let you know if it works! I'll also explain about the overdue dates preferred order.

Hi Dave,

I was hoping the desired results could be achieved with a sorting rule rather than a formula.
In hindsight I understand that my initial explanations were very bad so I'd like to try again with a new example.

Let's say that I have 4 projects and each project has 2 phases. I would like to know what kind of sorting rules I can apply in order to have, in relation to a specific date, on top and in descending order starting with the one that is closer to the given date, the projects that are currently in phase 2 (i.e. the date listed in phase 1 is passed) and then the projects that are still in phase 1, also in descending order but after those that are already in phase 2.

I have prepared a new example in the attachment, I hope this one is clearer.

Is this something that can be achieved with a custom sort option?

Originally Posted by FlameRetired

I don't know of a way to do this by sorting rules. I haven't done many of them, though.

Let me see if I can get us some help.

Thanks a lot for looking into this.

If sorting rules are not a viable solution a formula would be also fine of course.

Sorry I don't actually speak German, just live there.

I've added the behavior for each row below, let me know if this makes sense.

B9 -> check B2:B5 for cells that contain dates equal or before than the one in E7 and return the one that has in the cell next to it (the one in column C) the earliest date (smallest value) compared to the other dates in the C cells next to any cells in the B2:B5 range that contain dates equal or before than the one in E7. If there are 0 cells in the B2:B5 range containing a date equal or before than the one in E7 return the cell with the earliest date from the B2:B5 range. Make the rest fall into place with INDEX/MATCH.

B10 -> check B2:B5 for cells that contain dates equal or before than the one in E7 and return the one that has in the cell next to it (the one in column C) the second earliest date compared to the other dates in the C cells next to any cells in the B2:B5 range that contain dates equal or before than the one in E7. If there are 0 cells in the B2:B5 range containing a date equal or before than the one in E7, return the cell with the second earliest date from the B2:B5 range. If there are 1 cells in the B2:B5 range containing a date equal or before than the one in E7 (it will have been pulled in B9 so) return the cell with the earliest date from the B2:B5 range but exclude from this check the cell that has been pulled in B9. Make the rest fall into place with INDEX/MATCH.

B11 -> check B2:B5 for cells that contain dates equal or before than the one in E7 and return the one that has in the cell next to it (the one in column C) the third earliest date compared to the other dates in the C cells next to any cells in the B2:B5 range that contain dates equal or before than the one in E7. If there are 0 cells in the B2:B5 range containing a date equal or before than the one in E7, return the cell with the third earliest date from the B2:B5 range. If there are 1 cells in the B2:B5 range containing a date equal or before than the one in E7 (it will have been pulled in B9) return the cell with the second earliest date (because in this case the earliest date will have been pulled in B10) from the B2:B5 range but exclude from this check the cells that have been pulled in B9 and B10. If there are 2 cells in the B2:B5 range containing a date equal or before than the one in E7 (they will have been pulled in B9 and B10 so) return the cell with the earliest date from the B2:B5 range but exclude from this check the cells that have been pulled in B9 and B10. Make the rest fall into place with INDEX/MATCH.

B12 -> check B2:B5 for cells that contain dates equal or before than the one in E7 and return the one that has in the cell next to it (the one in column C) the fourth earliest date compared to the other dates in the C cells next to any cells in the B2:B5 range that contain dates equal or before than the one in E7. If there are 0 cells in the B2:B5 range containing a date equal or before than the one in E7, return the cell with the fourth earliest date from the B2:B5 range. If there are 1 cells in the B2:B5 range containing a date equal or before than the one in E7 (it will have been pulled in B9 so) return the cell with the third earliest date from the B2:B5 range but exclude from this check the cells that have been pulled in B9 and B10. If there are 2 cells in the B2:B5 range containing a date equal or before than the one in E7 return the cell with the second earliest date from the B2:B5 range but exclude from this check the cells that have been pulled in B9 and B10. If there are 3 cells in the B2:B5 range containing a date equal or before than the one in E7 return the cell with the earliest date from the B2:B5 range but exclude from this check the cells that have been pulled in B9, B10 and B11. Make the rest fall into place with INDEX/MATCH.

And so on.

1. Click on the "Select All" button at the intersection of your row and column headings. The cells in your worksheet highlight to confirm they're selected.

2. Switch to the "Data" tab in the Microsoft Excel ribbon and locate the "Sort & Filter" group. Click on the "Sort" option.

3. Click on the "Sort By" drop-down menu to select a column by name.

Hi guys,

Thanks a lot for the replies, unfortunately I am still not getting the solution I was hoping for. I'm going to close this thread soon and start a new one with a more detailed example, perhaps also describing what I am currently doing manually to get the rows sorted the way I want.

Hi Dave,

Thanks a lot for the new attempt. I tried changing cell B4 from "January 2" to "January 21" but the results in ranges A9:C12 and A16:C19 remained the same, can you confirm that this is the intended behavior of the formula?

If that's the case unfortunately it's different from what I need. When B4 is "January 21" the order should be Fire, Wind, Earth, Water in both A9:C12 and A16:C19, the reasoning being as follows.

For A9:C12-> Fire goes on top because column B (1 Jan) is before 14 Jan, this row is sorted, along with the other rows that have a number earlier than14 Jan in B, in ascending order based on the values in C. However, there are no other rows with values earlier than 14 Jan in B, so it's only Fire.
The other 3 rows have all values that are after 14 Jan they will be listed after the only row that have a number earlier than 14 Jan in B (Fire). These 3 are sorted in ascending order based on their B values so Wind (15 Jan) then Earth (20 Jan) and then Water (21 Jan).

For A16:C19->Fire and Wind are before 19 Jan, so they are listed on top in ascending order based on their C values, Fire (26 Jan) first and then Wind (28 Jan). Water and Earth are after Jan 19 so they are listed after, and in ascending order based on their B values, so Earth (20 Jan) then Water (21 Jan).

Actually my desired outcome is Fire, Wind, Earth and Water also in A16:A19, because water is one one day earlier than earth.

It might be better to drop the old example with dates entirely, I have attached another one with just values outlining 2 sample situations: one with the results when the info in the list is compared against "17" and one for when it's compared against "14". This is much closer to the system I actually use right know to sort stuff manually. As you can see, I am interested only in the content of the "End" column and I use color formatting to get a visual cue of what I want, this helps me find 3 groups which I then sort individually.

Please have a look and let me know if I managed to get my point across this time.

Originally Posted by JeteMc

1) If the end values in Phases 1,2 and 3 are less than the value in cell M1 then sort in ascending order of the end values in phase 3,
2) If the end values in Phases 1 and 2 are less than the value in cell M1 then sort in ascending order of the end values in phase 2,
3) If the end values in Phase 1 are less than the value in cell M1 then sort in ascending order of the end values in phase 1.

Thank you for putting it down in simple words. Your assessment is correct.

Hi JeteMc,

First of all, a huge Thank you!!! for managing to understand what I wanted. Your formula works exactly that way, it just needs a workaround for when there are identical values.

I would also like to apologize to everyone who helped for the bad explanations.

1) Names 4 through 7 are given in reverse order of what is given as desired, however when you look at the 14 sheet you see that the values for P7:P10 are in descending order which is opposite of the trend in other circumstances.

Sorry, I messed up there, they should have been in ascending order.

If I am correct then Deb (phase 2 end value of 11) would be listed before Hercules (16). If the example is correct, Hercules (phase 3 end value of 19) before Deb (27)

The example is correct in this case and I think your formula correctly reflects it.

1) If the end values in Phases 1,2 and 3 are less than the value in cell M1 then sort in ascending order of the end values in phase 3,
2) If the end values in Phases 1 and 2 are less than the value in cell M1 then sort in ascending order of the end values in phase 2,
3) If the end values in Phase 1 are less than the value in cell M1 then sort in ascending order of the end values in phase 1.

Thank you for putting it down in simple words. Your assessment is correct.

I looked back at these rules and maybe they are not really correct after all.
Let me reiterate the the formula works as I wanted so I am glad you got my point, but I was thinking that the actual rule explanation could be made simpler also to incorporate this part:

2) If the end values in Phases 1 and 2 are less than the value in cell M1 then sort in ascending order of the end values in phase 3 but only after then names are placed according to point #1.

In each row, G will never be lower than E and E will never be lower than C, so would this explanation make sense?

1) If the end values in Phase 2 are less than the value in cell M1 then sort in ascending order of the end values in phase 3
Then, for the rest:
2) If the end values in Phase 1 are less than the value in cell M1 then sort in ascending order of the end values in phase 2
Then, for the rest:
3) If the end values in Phase 1 are equal or higher than the value in cell M1 then sort in ascending order of the end values in phase 1

Lastly, regarding the solution for identical values, I don't have any particular preference as long as all rows are displayed (I tried changing M1 to "2" and 3 names went "N/A"), do you have any suggestion?

Ah one more thing, could you also help me get the whole set up as in the attached file, with the list in one tab and the formula, helper columns included, in the output tab.

The idea is that the "list" tab shows the data in alphabetical order and is sometimes updated with additional rows or different values, while the output tab will be used to get stuff sorted according to a given value when needed.

Hi again,

I have a couple of questions.

1) It looks sometimes a row returns "N/A" when certain values as in C1. This happens with 8, 10 and 11 just to give a few examples.
The same thing happens with other values as well, that seem to work ok now, but mess the results up if I sort the rows in "List" tab by name in alphabetical order. For example, with 2 in C1, Omar disappears from output when the names in list are sorted in ascending order and May disappears when they are sorted in descending order.
How can I fix that?

2) I would like this system to keep working even when rows with new data, still with the same kind of content as the other existing rows, are added to the "list" tab (to be accurate all the numbers in this Excel file will be ultimately replaced by dates, I hope the reasoning is the same). Rows in the list tab need to be sorted by name. I won't ever have more than 40 rows at the same time, but they will be added, removed or edited on a constant basis. The formulas you kindly came up with right know seem to take in account only rows 4 to 13 but unfortunately I have no idea of how I can expand it to make it work for a bigger range, like rows 4 to 44. I tried changing the values in the formulas found in "output" from 13 to 44, copy them down in the range from 4 to 44 and add numbers from 11 to 40 in A but it didn't really work so it must me more complicated that that.

I am sorry for my lack of excel knowledge. It's pretty bad. For example the purpose of COUNTIF in column XEV is completely lost to me. Once the formula is sorted out I'll try to break it down to understand its inner workings, hopefully with the help of someone here in the forum, but both for being able to understand it and adapt it to my specific needs, the simpler the final formula is the better would be.

Also, if you think I should move to a new thread please let me know.

Originally Posted by JeteMc

I am glad that you did more testing, just had a feeling that there would be issues. I have addressed the issues given in point #1 by modifying the formulas in the columns with the heading Rank. One example of the change is the formula in XFC4 which now reads:

Formula:

=IFERROR(RANK(XFB4,XFB$4:XFB$13,1)+MAX(XFA$4:XFA$13,XEY$4:XEY$13,XEW$4:XEW$13)+COUNTIF(XFC$3:XFC3,RANK(XFB4,XFB$4:XFB$13,1)+MAX(XFA$4:XFA$13,XEY$4:XEY$13,XEW$4:XEW$13)),"")

I played around with the file a bit more and the above formula seems to work only when the identical results are 2, would you be able to advise on a workaround for cases where they are 3 or even more?

Thanks a lot for the quick reply.

If I don't hear for you for a while or time becomes an issue I'll start another thread but in general I don't mind waiting, I'll try to figure out what you did so far in the meantime (I didn't even know how to make a table tbh).

Hi, there is another issue I'd like to solve.

Deleting any row from 'List' seems to mess up all the results in 'Output'. The results deviate from the rule also in case a new row is inserted between the existing ones in 'List'.

Is there a way around that?

Understood, thank you again!

Thank you, I'll test it soon and let you know in case I have trouble with it!

It's working perfectly