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.
Hi marameleo and welcome to the forum.
Quick question.
In column C, Phase 2 starting values include 30-Jul in cell C5. They look like this and expected values are all Mays and Junes ... no Julys. Is this a typo or intended? If July is intended in the expected results it could make a difference. Which one(s) need to be changed?
Last edited by FlameRetired; 01-23-2017 at 10:05 PM.
Dave
I went ahead and assumed that the dates in the DESIRED RESULTS sections were intended to be 30-July.
I can get this formula to work in the 1-Jun section only. It is array entered in A8 then filled down and across to C11. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.It returns the expected results for that section.Formula:
Please Login or Register to view this content.
When applied to 8-Jun and 22-Jun sections it fails completely in 8-Jun and only partially works in 22-Jul. It's as if the desired results don't follow the rule in these sections.
Also how did you want the remaining dates sorted that are not after a given date?
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?
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.
Ich verstehe nur Bahnhof. Vielleicht wäre Clever Excel forum (Deutschsprachig) einfacher zu verwenden.
Sorry I don't actually speak German, just live there.
Thanks a lot for looking into this.Originally Posted by FlameRetired
If sorting rules are not a viable solution a formula would be also fine of course.
I've continued with formula attempts. I've gotten no further than before.
Once again I can get the 'Sorted for January 14' section to produce the expected results. Although these results seem to contradict the instructions.
I used this array formula in A9 filled down and across to C12.Formula:Please Login or Register to view this content.
I can not reconcile how the results below (for 'Sorted for January 19') tally with the instructions.
I also can find no formula that replicates the expected results for 'Sorted for January 19'.
Would you double check the instructions, and even consider stating ... line by line / date column by column ... how these relate? This will take some time and work, but I know of no other way.
If we can solve just one column (A, B or C) the others will fall into place with a simple INDEX / MATCH.
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.
OK.
This returns the expected results.
There are two helper columns. Column E (cells E7 and E14 only) mark the changeover dates.
In column F this formula returns a contiguous range of repeating dates. It shortens the final formula. In F7 and filled down.Formula:Please Login or Register to view this content.
Then array enter this in C9 and fill down to C12.Formula:Please Login or Register to view this content.
In C16 array entered as the previousFormula:Please Login or Register to view this content.
Then in A9 filled down and across to B12 this non-array formula.Copy that whole range and paste into cell A16. You may have to reformat the numbers as dates.Formula:Please Login or Register to view this content.
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).
Yes it is the intended behavior based upon my understanding(s) of previous instructions.
Now as i understand it ... based solely upon the above orders of Fire, Wind, Earth and Water (A9:A12) and Fire, Wind, Water and Earth (A16:A19 this is the desired outcome.
However the order of the dates bears little resemblance to what you describe.
!!?!!
Row\Col PHASE 1 ends on PHASE 2 ends on Earth Fire Water Wind Sorted for January 14 Fire Wind Earth Water Sorted for January 19 Fire Wind Water Earth
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.
It seems to me that we need some explicit rules as to how the values need to be sorted.
Going back to the file attached to post #18 it seems that the rules could be written
1) If the values in B2:B5 are less than or equal to the value in E7 then the names in column A are arranged in the ascending order of the values in C2:C5
2) If the values in B2:B5 are greater than the value in E7 then the names in column A are arranged in the ascending order of the values in B2:B5
I feel that we need similar rules for sorting the names in the file attached to post #20.
It seems to me that the rules will read something like:
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.
I'd like to get some confirmation of these rules before attempting to write any formulas.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thank you for putting it down in simple words. Your assessment is correct.
Looking at this again, either I am correct or the example is. 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), then for point 2 the rule should look more like:
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.
Let us know which is correct and if you have any questions.
From post #20
I can't keep up. I'll have to bow out.Actually my desired outcome is Fire, Wind, Earth and Water also in A16:A19, because water is one one day earlier than earth.
I'm still subscribed though. So I'll check in from time to time.
I have gone ahead and put together some formulas that will sort the names and populate the table in M4:R13 (on the 17 sheet only). There are helper columns in columns XEV:XFD which set up the order to be used in sorting the names. The actual Names column (L) is populated using the formula:The values table is populated using the formula:Formula:Please Login or Register to view this content.Expected results are displayed on the 17 sheet when 17 is in M1, however that isn't the case when the value in M1 is changed to 14. There are two issues:Formula:Please Login or Register to view this content.
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.
2) There are identical values in N11:N12 which causes there to be an 8th name but no 9th name. The underlying logic as to which name appears 8th and which appears 9th needs to be worked out in order to modify or rewrite the formula(s).
Let us know if you have any questions.
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.
Sorry, I messed up there, they should have been in ascending order.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.
The example is correct in this case and I think your formula correctly reflects it.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)
I looked back at these rules and maybe they are not really correct after all.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.
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:
In each row, G will never be lower than E and E will never be lower than C, so would this explanation make sense?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.
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.
Since you said "I don't have any particular preference as long as all rows are displayed" my proposed solution is adding the count of identical rankings previously in the column to the ranking, as in XFC4:This will probably need to be tested more thoroughly.Formula:Please Login or Register to view this content.
Attached is a version of the file with the list on one tab and the output on the other. Note that the numbering that was previously in column K is integral and is kept in column A of this version, however it may be hidden (as shown in the attached file) for aesthetic purposes.
Let us know if you have any questions.
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.
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:The best way that I can think of to make your table on the List sheet dynamic is to change it from a range to a table. I did that on the attached copy of the file and sorted the names in ascending order. The formulas on the Output Tab sheet will need to be changed to reflect there references to table columns before the change will be effective for adding rows to the 'List' table. I will have time to do that later, if you have time to wait. If not you may want to consider starting a new thread (more eyes on a problem the better anyway) that includes a link to this thread.Formula:Please Login or Register to view this content.
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).
Here is a copy of the file that has been set up to accommodate 40 names with corresponding rows of data. Dates should work fine as Excel actually uses integer numbers to represent the number of days since 1 Jan, 1901 then formatting is applied to make it look like a date.
Let us know if you have any questions.
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?
In the case of removing a name from the 'List' table I would suggest that you select the cells in that row and press the delete key. For example, using the file attached to post #32, if you select List!A8:G8 and press the delete button you'll delete Hercules and his phase data. If you then look at the Output Tab sheet you'll see that table now only has 9 names and that they are still in order based on the formulas in XEV:XFD. You could press the sort/filter (drop down) button in the column header cell, A4, and apply an 'A to Z' sort (again) to get rid of the row of blank cells.
In the case of adding a name my suggestion would be to type in the new name in one of the empty rows and then resort. For example type the name Barbara in List!A17 then type in the following phase values, 26,27, 28, 29, 30, and 31. You'll see that Barbara and her data has been added to the Output Tab table. Now resort, 'A to Z' and Barbara and her data get moved to row 6 of the List table, remaining as the last name (because of the phase data) on the Output Tab table.
If you actually need to add more rows to the List table select the last column of the last row of the table, G43, and press the tab key.
Let us know if you have any questions.
Last edited by JeteMc; 02-05-2017 at 04:02 PM.
Understood, thank you again!
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
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?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:Please Login or Register to view this content.
Lets try formulas similar to the following, found in XFC4 of the attached file:Let us know if you have any questions.Formula:Please Login or Register to view this content.
Thank you, I'll test it soon and let you know in case I have trouble with it!
It's working perfectly
Good to hear. The credit for the formula goes to Debra at the Contextures web site. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks