# Thread: Sorting data by lowest date

Originally Posted by Omer1234
Thanks Brian, I appreciate you trying !!!!! .....

1 - see my comments in red. Do I need to have Date Rank and Row in the FEB worksheet? Can we achieve it without these 2 columns? OR Can it be combined into 1 column?
2 - The reason I asked is because I have 12 worksheets (one for each month) and I wanted to limit it to minimum functions if possible.

Thx
The purpose of ranking the dates is so that we have some order in which to sort them. I make the blanks zero so that I can sort those as well so they will be included on the summary. The date rank and row could be combined I suppose. I added row to distinguish between duplicate dates.

2. THANKS FOR EDUCATING ME, since I want to make sure tha tif I need to tweak it, I understand what to do in the future.

In the Feb Tab.....What is this formula doing? (=IFERROR(RANK(J2,\$J\$2:\$J\$20),0))........I can see its ranking the date, but how did 1/8/16 get a ranking of 13? Being the smallest, should this be ranked 1? The smallest number is the lowest rank.

In the Feb Tab.....What is this formula doing? (=COUNTIF(\$K\$1:K2,K2)).....I see its counting It counts the number of instances a value has occured in the rows above and including the current row. This combined with the rows list in the February tab is what allows the index and match functions to handle duplicates. It distinguishes rows with duplicate dates.

Can these 2 be combined into They could.

In the Summary Tab ...... Column C (Date Rank) .....(=IF(ISNUMBER(\$B8)=TRUE,LARGE(Feb!\$K\$2:\$K\$20,Summary!B8),""))....... so its Ranking again?........If so why cant we just copy from FEB tab and sort, OR maybe that's what you are doing : it isn't ranking, it is finding the first largest from the february tab. I'm assuming that you can't just sort this in the February tab (or put it in a pivot table) and need to do this with formulas. So in the February tab we do two things 1) rank the dates and 2) put in a row number to have something to distinguish between duplicates. These probably could be combined, but I thought it made it clearer to separate them.

Then in column D..........I see you ranking ....... Again can these be combined into 1?in column d we are counting not ranking. We need a way to tell our lookup formula that we are looking for the nth instance of a date when there are duplicates.

Lastly, what is the purpose of Cntrl+alt+Enter {}........ How does that make it different from just pressing enter the functions are array formulas; the index and match function is concatenating the ranges it is looking up within the formula. To do that you need to tell excel that this is an array function which we do by pushing ctrl + shift + enter which is what gets the curly brackets. If you go into one of those functions and press f2 and then just press enter it won't evaluate correctly..

3. OK last one..... in summary tab column H for the first entry on 1/8/16, I changed formula to {=IF(ISNUMBER(\$B8)=TRUE,INDEX(Feb!F:F,MATCH(Summary!\$C8&Summary!\$D8,Feb!\$K\$2:\$K\$20&Feb!\$L\$2:\$L\$20,0)),"")} to pickup Column F value in FEB worksheet...... Why didn't it change value to \$3044? Yes, I did cntrl+shift+enter :-t selecting the whole column F makes the arrays in the index/match different sizes. If you select F2:F20 it should work (or the entire columns in the other parts of the formula as long as you're consistent. However in this case I don't recommend that because it will try to concatenate 1m rows.)

Thanks Brian

4. Originally Posted by Omer1234
How can I setup the summary tab so that this time COST 2 is the Highest to lowest rather than date :-)....+++ (date, Order # , & throw in the Gallons column form Feb worksheet)
B

you have been absolutely great so far and I appreciate it

1- can you put up a final version with column j&k combined in February worksheet? (Sorting and ranking)
2- sort summary for cost 2 as the criteria (largest to smallest)
3- I don't know if we can display every month with drop down(see my earlier post) OR maybe a spot where I enter 3/1-3/31 and it only search within those dates?

A BIG THANKS!!!!!!

5. Originally Posted by Omer1234
LAST QUESTION......Promise

If I added a Mar worksheet & added more data, can the summary page auto calculate for March? OR do I have to go in and copy formulas for the march worksheet...........In other words is it easy to create a "drop down for the months, such that when I pull down any month it displays data for that month (YES I know I am getting in to VBA, but at the same time trying to avoid it since I want to be able to copy these functionalities to other spreadsheets I have without asking you every time)

THANKS A LOT!!!!!
Assuming March is a separate tab name "Mar" the attached sheet should do that. I created a March tab and added \$100 to each cost and 30 days to each date that wasn't blank. The trick to this is creating a drop down list from your tab names and linking the formulas to pull from those tabs using the indirect function. Not sure if you've used that one much, if not it takes the text in the cell reference and applies it in the function. So if we write our formula and name our tabs correctly it isn't too bad. The formulas are a bit hard to read however. Let me know if you have questions on the attachment. I'm a little too tired right now to write it up in much more detail but I will respond tomorrow if something doesn't make sense.

6. Originally Posted by Omer1234
How can I setup the summary tab so that this time COST 2 is the Highest to lowest rather than date :-)....+++ (date, Order # , & throw in the Gallons column form Feb worksheet)
You would change the date rank column in the data spreadsheets to reference the Cost 2 column. If you want it highest to lowest select 0 for descending in the rank function. In the summary tabs the date rank formula needs to be updated from "Large" to "Small". You could automate this with additional drop downs if you wanted.

A word of warning - these formulas are complicated and if something goes wrong it isn't always so easy to fix. Putting all of your data tabs in one sheet and summarizing in a pivot table is a lot less prone to error or confusion.

7. Originally Posted by Brian Smith
You would change the date rank column in the data spreadsheets to reference the Cost 2 column. If you want it highest to lowest select 0 for descending in the rank function. In the summary tabs the date rank formula needs to be updated from "Large" to "Small". You could automate this with additional drop downs if you wanted.

A word of warning - these formulas are complicated and if something goes wrong it isn't always so easy to fix. Putting all of your data tabs in one sheet and summarizing in a pivot table is a lot less prone to error or confusion.

Ok will take a look tomorrow and give feedback...... Thanks for everything

8. Originally Posted by Omer1234
B

you have been absolutely great so far and I appreciate it

1- can you put up a final version with column j&k combined in February worksheet? (Sorting and ranking)
2- sort summary for cost 2 as the criteria (largest to smallest)
3- I don't know if we can display every month with drop down(see my earlier post) OR maybe a spot where I enter 3/1-3/31 and it only search within those dates?

A BIG THANKS!!!!!!

Hi Brian,

Any update?

Thx

9. Originally Posted by Omer1234
Hi Brian,

Any update?

Thx
I'm at work I will see what I can do tonight.

10. See attached. I added tabs for all of the months and updated the summary tab to sort by cost 2 in descending order.

Upon further review combining the rank and the instance count could potentially create errors and I don't think is a good idea.

The date tabs go down 500 rows. If you have more than that you need to drag down the formulas in the blue columns AND update the bottom row of the range in the summary column so it picks up the whole range.

Page 2 of 3 First 1 2 3 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•