+ Reply to Thread
Results 1 to 19 of 19

Sorting Notes

  1. #1
    Registered User
    Join Date
    07-05-2017
    Location
    NC
    MS-Off Ver
    2016
    Posts
    11

    Sorting Notes

    Hi,

    I am a total beginner, so I'm not even sure how to word this. I imported some notes into excel from a text file. As you can (hopefully) see from the attachment, there are 3 columns. "A" is the note, "B" is the date created, and "C" is the date modified.

    I would like to sort these notes chronologically based on the date modified. You'll notice the notes sometimes take up multiple rows and have lots of blank space. How can I tell Excel to essentially group each multi-row note (the first row is always the one with the dates on it), and then sort based on the date modified?

    Thanks!
    Attached Images Attached Images
    Last edited by Free6000; 07-06-2017 at 03:20 PM.

  2. #2
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Sorting Notes

    It would really be easy for us if you could upload your spreadsheet here so we can have a better look at the arrangement of the data.
    You can find the link regarding instructions on uploading workbooks at this forum in my signature below.
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh

  3. #3
    Registered User
    Join Date
    07-05-2017
    Location
    NC
    MS-Off Ver
    2016
    Posts
    11

    Re: Sorting Notes

    Thanks for the reply! Unfortunately these are all of my notes from the last 5 years or so and I'm sure it contains personal data that I can't share online.

  4. #4
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Sorting Notes

    No problems, you can just share a few rows, you can change all the text or any information that is private. It will save our time retyping the info.

  5. #5
    Registered User
    Join Date
    07-05-2017
    Location
    NC
    MS-Off Ver
    2016
    Posts
    11

    Re: Sorting Notes

    Alright, here is a sample workbook attached. It has 5 notes, and I would like for them to be sorted chronologically based on the date modified. So the order I would like for them to be in is:

    This is a note
    New note
    Last note
    This is another note
    Yet another note
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-05-2017
    Location
    NC
    MS-Off Ver
    2016
    Posts
    11

    Re: Sorting Notes

    Thanks again for helping

  7. #7
    Registered User
    Join Date
    07-05-2017
    Location
    NC
    MS-Off Ver
    2016
    Posts
    11

    Re: Sorting Notes

    But don't take that thanks to mean it's been solved. Definitely still don't know what to do!

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,981

    Re: Sorting Notes

    Here's one option for you to consider:

    You can create a helper column with the following formula:

    =IF(C1="","",SUBSTITUTE(RIGHT(C1,LEN(C1)-10),",","",2)+0)

    Then sort based on that column.
    Last edited by 63falcondude; 07-06-2017 at 10:35 AM.

  9. #9
    Registered User
    Join Date
    07-05-2017
    Location
    NC
    MS-Off Ver
    2016
    Posts
    11

    Re: Sorting Notes

    Thanks 63falcondude. I really don't even know the basics of sorting, not a regular Excel user. So would I just select column D, and paste that formula in? Then how would I go about sorting?

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,981

    Re: Sorting Notes

    I just realized that there are rows that have to stay together. Try this:

    1) Make sure that you have a header row in row 1.
    2) Enter this formula into D2 =IF(C2="",D1,SUBSTITUTE(RIGHT(C2,LEN(C2)-10),",","",2)+0) then drag the formula down column D.
    3) Select/Highlight columns A:D
    4) Data > Sort > "My data has headers" should be checked > Sort by column D header > OK

    Edit: See attached workbook for context. I have colored the groups that need to stay together for visual aid.
    Attached Files Attached Files
    Last edited by 63falcondude; 07-06-2017 at 11:54 AM.

  11. #11
    Registered User
    Join Date
    07-05-2017
    Location
    NC
    MS-Off Ver
    2016
    Posts
    11

    Re: Sorting Notes

    YES! I got it!

    Thanks so much 63falcondude, never would have figured that out on my own!!

  12. #12
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,981

    Re: Sorting Notes

    You're welcome. Glad we could help!

    Welcome to the forum.

  13. #13
    Registered User
    Join Date
    07-05-2017
    Location
    NC
    MS-Off Ver
    2016
    Posts
    11

    Re: Sorting Notes

    Ooo one more question. If the dates are on the last line in a row instead of the first (new workbook attached), how would I modify that formula to do the same thing?
    Attached Files Attached Files

  14. #14
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,981

    Re: Sorting Notes

    Try this in D2

    =INDEX(SUBSTITUTE(RIGHT(C2:C$32,LEN(C2:C$32)-10),",","",2)+0,MATCH("Modified*",C2:C$32,0)) Ctrl Shift Enter

  15. #15
    Registered User
    Join Date
    07-05-2017
    Location
    NC
    MS-Off Ver
    2016
    Posts
    11

    Re: Sorting Notes

    It works for a few of them and then it gets hung up. I've attached a segment of the actual notes file after I ran the formula.
    Attached Files Attached Files

  16. #16
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,981

    Re: Sorting Notes

    A couple of reasons why the formula did not work on this new set of data.

    1) The ranges in the formula need to be adjusted to match the range of your data (i.e. C$32 has to be changed to C$53 or whatever is the last cell in column C)
    2) You did not confirm the formula using Ctrl Shift Enter. This is an array formula that requires you to hit Ctrl+Shift+Enter instead of just Enter.

    That being said, given the new example that you shared in post #15, try this in D2
    =INDEX(SUBSTITUTE(RIGHT(C2:C$53,LEN(C2:C$53)-10),",","",2)+0,MATCH("Modified*",C2:C$53,0)) Ctrl Shift Enter

    If you entered the formula correctly, {brackets} should surround your formula like this {=formula}

  17. #17
    Registered User
    Join Date
    07-05-2017
    Location
    NC
    MS-Off Ver
    2016
    Posts
    11

    Re: Sorting Notes

    Bingo. I definitely did hit Ctrl Shift Enter but I didn't know about the ranges. Got it now, thanks again

  18. #18
    Registered User
    Join Date
    07-05-2017
    Location
    NC
    MS-Off Ver
    2016
    Posts
    11

    Re: Sorting Notes

    ~~~~~~~~~~

  19. #19
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,981

    Re: Sorting Notes

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Add notes from 6 empolyees
    By Michael Lerena in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-25-2017, 10:44 AM
  2. Embed image into Lotus Notes with VBA - not using Notes.NotesUIWorkspace
    By pmor1503 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-11-2014, 11:49 PM
  3. Weird thank you notes
    By Ace_XL in forum The Water Cooler
    Replies: 4
    Last Post: 06-25-2013, 03:23 PM
  4. end notes, foot notes and an Index Table
    By thadacto in forum Word Formatting & General
    Replies: 1
    Last Post: 12-27-2011, 06:22 PM
  5. notes
    By ADK in forum Excel General
    Replies: 4
    Last Post: 10-06-2005, 02:05 PM
  6. Email with notes
    By Nigel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-27-2005, 04:05 PM
  7. Notes (not Comments)
    By daniels012 in forum Excel General
    Replies: 3
    Last Post: 01-18-2005, 01:39 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1