Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 23

Thread: Sorting data by lowest date

  1. #1

    Sorting data by lowest date



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Silly question - could you just put it in a pivot table (see attached)?
    Attached Files Attached Files

  3. #3
    Quote Originally Posted by Brian Smith View Post
    Silly question - could you just put it in a pivot table (see attached)?
    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

  4. #4
    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?

  5. #5
    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.
    Attached Files Attached Files

  6. #6
    Quote Originally Posted by Brian Smith View Post
    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.

    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

  7. #7
    Quote Originally Posted by Omer1234 View Post
    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




    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.

  8. #8
    Quote Originally Posted by Brian Smith View Post
    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.

    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

  9. #9
    Quote Originally Posted by Brian Smith View Post
    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.

    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!!!!!

  10. #10
    Quote Originally Posted by Brian Smith View Post
    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.

    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)

Page 1 of 3 1 2 3 LastLast

Posting Permissions

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