Sorting data by lowest date

O

Omer1234

Member
Joined
Apr 12, 2016
Messages
47
Reaction score
0
Points
6
Excel Version(s)
Excell 97-2003
Hi ,

I could use some help here.

The first worksheet "feb" is raw data"
The second worksheet in tehi sspreadsheet "summary" is where I need help.

I need the data sorted by date "Smallest to Highest" and then the corresponding values, which I though could be done with VLOOKUP but I am unable to sort the dates ( The one tricky part is the empty date in worksheet FEB, I need this to be part of the data even though there is not date assigned to it)

THANKS for the help in advance.
 

Attachments

  • Sample Data.xlsx
    15.9 KB · Views: 8
Silly question - could you just put it in a pivot table (see attached)?
 

Attachments

  • ProductionStatusSummary.xlsx
    24.1 KB · Views: 7
Brian Smith said:
Silly question - could you just put it in a pivot table (see attached)?
Click to expand...

BrIan,

i prefer to to accomplish this with a formula......since I have worksheet with all 12 months.

thx for trying with a formula. ( I also not to want to use data-> sort)

THX
 
I assumed there was a reason you hadn't gone that route, but figured worth a try.

Does the summary have all of the rows that the data file has? In other words is it truly just sorting by date including the blanks and bringing over the data or is it aggregated in some way?
 
See if this helps.

In the data section I added two columns
1) ranks the dates and makes a blank 0
2) counts the instances of each date since there are duplicates

In the summary tab I am using the "Large" formula to reproduce the list of date ranks and then performing the same count instances function. With these two columns which sort the data (I believe) in the way you were looking for we do an index and match function to the those two concatenated arrays in the data table. The index and match functions are all array formulas (ctrl + shift + enter). I added a condition to the number column in the left to add by one until it gets to the amount of values in the data table. Then it enters "End" then blanks. When the other formulas encounter a non numerical value in the first column they evaluation as blanks. When the data grows, the formulas should populate further down the columns.
 

Attachments

  • ProductionStatusSummarySortDuplicatesAndBlanks.xlsx
    34 KB · Views: 14
Brian Smith said:
See if this helps.

In the data section I added two columns
1) ranks the dates and makes a blank 0........... What is the purpose here?
2) counts the instances of each date since there are duplicates.........

In the summary tab I am using the "Large" formula to reproduce the list of date ranks and then performing the same count instances function. With these two columns which sort the data (I believe) in the way you were looking for we do an index and match function to the those two concatenated arrays in the data table. The index and match functions are all array formulas (ctrl + shift + enter). I added a condition to the number column in the left to add by one until it gets to the amount of values in the data table. Then it enters "End" then blanks. When the other formulas encounter a non numerical value in the first column they evaluation as blanks. When the data grows, the formulas should populate further down the columns.
Click to expand...


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
 
Omer1234 said:
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
Click to expand...





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?




In the Feb Tab.....What is this formula doing? (=COUNTIF($K$1:K2,K2)).....I see its counting,


Can these 2 be combined into 1?




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 :)


Then in column D..........I see you ranking ....... Again can these be combined into 1?


Lastly, what is the purpose of Cntrl+alt+Enter {}........ How does that make it different from just pressing enter.
 
Brian Smith said:
See if this helps.

In the data section I added two columns
1) ranks the dates and makes a blank 0
2) counts the instances of each date since there are duplicates

In the summary tab I am using the "Large" formula to reproduce the list of date ranks and then performing the same count instances function. With these two columns which sort the data (I believe) in the way you were looking for we do an index and match function to the those two concatenated arrays in the data table. The index and match functions are all array formulas (ctrl + shift + enter). I added a condition to the number column in the left to add by one until it gets to the amount of values in the data table. Then it enters "End" then blanks. When the other formulas encounter a non numerical value in the first column they evaluation as blanks. When the data grows, the formulas should populate further down the columns.
Click to expand...


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 :)

Thanks Brian
 
Brian Smith said:
See if this helps.

In the data section I added two columns
1) ranks the dates and makes a blank 0
2) counts the instances of each date since there are duplicates

In the summary tab I am using the "Large" formula to reproduce the list of date ranks and then performing the same count instances function. With these two columns which sort the data (I believe) in the way you were looking for we do an index and match function to the those two concatenated arrays in the data table. The index and match functions are all array formulas (ctrl + shift + enter). I added a condition to the number column in the left to add by one until it gets to the amount of values in the data table. Then it enters "End" then blanks. When the other formulas encounter a non numerical value in the first column they evaluation as blanks. When the data grows, the formulas should populate further down the columns.
Click to expand...


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!!!!!
 
Brian Smith said:
See if this helps.

In the data section I added two columns
1) ranks the dates and makes a blank 0
2) counts the instances of each date since there are duplicates

In the summary tab I am using the "Large" formula to reproduce the list of date ranks and then performing the same count instances function. With these two columns which sort the data (I believe) in the way you were looking for we do an index and match function to the those two concatenated arrays in the data table. The index and match functions are all array formulas (ctrl + shift + enter). I added a condition to the number column in the left to add by one until it gets to the amount of values in the data table. Then it enters "End" then blanks. When the other formulas encounter a non numerical value in the first column they evaluation as blanks. When the data grows, the formulas should populate further down the columns.
Click to expand...


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)
 
Omer1234 said:
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
Click to expand...

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.
 
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..
 
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
 
Omer1234 said:
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)
Click to expand...
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!!!!!!
 
Omer1234 said:
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!!!!!
Click to expand...

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.
 

Attachments

  • ProductionStatusSummarySortDuplicatesAndBlanksDropdown.xlsx
    41.2 KB · Views: 7
Omer1234 said:
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)
Click to expand...

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.
 
Brian Smith said:
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.
Click to expand...



Ok will take a look tomorrow and give feedback...... Thanks for everything
 
Omer1234 said:
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!!!!!!
Click to expand...




Hi Brian,

Any update?

Thx
 
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.
 

Attachments

  • ProductionStatusSummarySortDuplicatesAndBlanksDropdown (final).xlsx
    263.1 KB · Views: 16
You must log in or register to reply here.
Back
Top