Sorting notes in the same cell?

[Add365]

Hi,

I wonder if anyone can help? I have been asked by someone at work that has a data entry query.

They have a sales spreadsheet which 4 people use and they have an end cell where they enter comments like "Quoted a full load to Sweden 1/9/23" & "emailed to chase quote to Sweden 10/9/23"

The comments entered above would be in the same cell and each customer would have there own row in the excel spreadsheet. My colleague has aksed can excel sort the most recent comments to the top of the cell if all comments are in the same cell?

They would like this done so the next sales person can see the most recent comment at the top of the cell and when they enter their new comment it automatically goes to the top of the same cell, is this even possible?

 

[footoo]

Are the comments always in the same column?
If so, what column?
How are the comments separated within each cell?

 

[Add365]

Hi, sorry for the late response, Yes the comments are always in the same column which is column M.
Each customer has there own cell so it may contain 5 or 6 comments all entered at differant times. At the moment they have been putting all the comments in the same cell just with a date marker at the end to show when the comment was entered.
See image below this is 2 cells of comments

 

[dreid1011]

Couldn't the sales people enter the comments at the beginning of the cell themselves? When they select the cell to enter their comments, they can go to the formula bar and put their cursor at the beginning before they type. Then would just have to add a line return after they're done (alt + enter).

Edit: and keyboard shortcut to get there faster: F2, then Home.

 

[footoo]

Try the macro below.
It is assumed that :
• The comments start in M2.
• Each line within each cell is separated by a carriage return (Alt+Enter).
• The dates are all valid (e.g. no dates like 31.2.23).
• All dates are in the format dd.mm.yy
• There is always a space before each date.

Sub v()
Dim sh As Worksheet, rng As Range, cel As Range, r As Range
Dim ray, part, ry
Set sh = ActiveSheet
Set rng = Range([M2], Cells(Rows.Count, "M").End(3))
With Application
    .ScreenUpdating = False
    .EnableEvents = False
Worksheets.Add(After:=ActiveSheet).Name = "Temp"
sh.Activate
For Each cel In rng
    With Sheets("Temp")
        .Cells.ClearContents
        ray = Split(cel, Chr(10))
        For part = LBound(ray) To UBound(ray)
            .[A1].Offset(part) = ray(part)
        Next
        Set r = .Range(.[A1], .Cells(Rows.Count, "A").End(3))
        r.Offset(0, 1).Formula = "=IF(ISNUMBER(RIGHT(A1,2)+0),TEXTAFTER(A1,"" "",-1),A1)"
        r.Offset(0, 2).Formula = "=IFERROR(DATEVALUE(SUBSTITUTE(B1,""."",""/"")),C2)"
        r.EntireRow.Sort Key1:=.[C2], Header:=xlNo, Order1:=xlDescending
        ry = Application.Transpose(r)
        cel.Value = Join(ry, Chr(10))
    End With
Next
    .DisplayAlerts = False
Sheets("Temp").Delete
    .DisplayAlerts = True
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

 

[Add365]

Hi,

Thanks but it is coming up with an error, when I debug it highlights the below

cel.Value = Join(ry, Chr(10))

 

[footoo]

It works for me. I don't know what to suggest.
What does the error message say?
Does the error occur when processing the first data cell in column M, or some subsequent cell?
Have a look at the Temp sheet - are there any formula errors in columns B and C?

 

[footoo]

Also, on the Temp sheet, do the cells in column A match each line of the cell being processed?

 

[Add365]

Hi, I have tried again with a blank sheet and just data in column M but I cant get it to work so I am going to go with the sales people entering the comments at the beginning of the cell themselves. They can just go into the formula bar and put their cursor at the beginning before they type and then press (alt + enter) to add a new line.
Thanks for your help though on this one