Sorting Notes

**Free6000** · 07-05-2017, 01:14 PM

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!

**sourabhg98** · 07-05-2017, 01:31 PM

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.

**Free6000** · 07-05-2017, 01:33 PM

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.

**sourabhg98** · 07-05-2017, 01:36 PM

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.

**Free6000** · 07-05-2017, 04:23 PM

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

**Free6000** · 07-05-2017, 05:11 PM

Thanks again for helping

**Free6000** · 07-06-2017, 10:20 AM

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

**63falcondude** · 07-06-2017, 10:33 AM

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.

**Free6000** · 07-06-2017, 11:19 AM

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?

**63falcondude** · 07-06-2017, 11:44 AM

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.

**Free6000** · 07-06-2017, 01:02 PM

YES! I got it!

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

**63falcondude** · 07-06-2017, 01:08 PM

You're welcome. Glad we could help!

Welcome to the forum.

**Free6000** · 07-06-2017, 01:53 PM

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?

**63falcondude** · 07-06-2017, 02:05 PM

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

**Free6000** · 07-06-2017, 02:16 PM

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.

**63falcondude** · 07-06-2017, 02:26 PM

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}

**Free6000** · 07-06-2017, 03:17 PM

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

**Free6000** · 07-06-2017, 03:17 PM

~~~~~~~~~~

**63falcondude** · 07-06-2017, 03:35 PM

You're welcome.

Sorting Notes

LinkBack

Thread Tools

Rate This Thread

Display

Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Re: Sorting Notes

Thread Information

Users Browsing this Thread

Similar Threads

Add notes from 6 empolyees

Embed image into Lotus Notes with VBA - not using Notes.NotesUIWorkspace

Weird thank you notes

end notes, foot notes and an Index Table

[SOLVED] notes

[SOLVED] Email with notes

Notes (not Comments)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions