Reorder cell contents in reverse order - YIKES!!! quick resolution required!!

[ShuStar]

Hi all,

Got a sticky situation that is stressing me out, so need expert help!!

I need to change the cell contents in reverse order - kind of sorting the cell Z-A but by line.

I want to reverse the cell layout so that the latest comment is at the top. ie Comment 5 followed by 4,3,2,1
(the comments at the bottom of the cell from my extract is the Latest comment, therefore would like this to show at the top).

There can be more than 5 comments, this is only an example*Essentially I want to reverse the content in the cell by line, re ordering the cell by Comment 5,4,3,2,1
*I guess its like Sorting the cell Z-A but by line
*I would like the easiest way to do this, prefereably by formula but if there is a macro, then this is fine.
*If the resolution will get too messy (as there can be more than 5 comments), then if is there a way to extract the last 3 latest comments, then I am happy with this....(ie Comments 5,4,3 and in the order so that Comment 5 is at the top, followed by 4 and 3)

I am open to ideas, ie formulas split across columns and then concatonated etc

Hope this makes sense. An example of what I am facing and the format I'd like it is below.

Thanks in advance!!!
[TABLE="width: 413"]
<tbody>[TR]
[TD]Product ID
[/TD]
[TD]Comments Column Currently
[/TD]
[TD]Ideal Comment Layout
[/TD]
[/TR]
[TR]
[TD]QQ
[/TD]
[TD]Comment 1
Comment 2
Comment 3
Comment 4
Comment 5
[/TD]
[TD]Comment 5
Comment 4
Comment 3
Comment 2
Comment 1
[/TD]
[/TR]
[TR]
[TD]AA
[/TD]
[TD]Comment 1
Comment 2
Comment 3
[/TD]
[TD]Comment 3
Comment 2
Comment 1
[/TD]
[/TR]
[TR]
[TD]ZZ
[/TD]
[TD]Comment 1
Comment 2
Comment 3
Comment 4
[/TD]
[TD]Comment 4
Comment 3
Comment 2
Comment 1
[/TD]
[/TR]
[TR]
[TD]WW
[/TD]
[TD]Comment 1
Comment 2
[/TD]
[TD]Comment 2
Comment 1
[/TD]
[/TR]
</tbody>[/TABLE]

 

[svendiamond]

I'm assuming there is a space in between the last comment of QQ, and the first comment of AA, etc, etc? Always an empty line right?

 

[ShuStar]

I'm assuming there is a space in between the last comment of QQ, and the first comment of AA, etc, etc? Always an empty line right?

Hi,

Thanks for replying

QQ, AA, ZZ, WW etc are individual cells.

All the comments in QQ are aligned to QQ only.

So in Excel:

Cell A2=QQ , Cell B2= Comment 1,2,3,4,5 seperated by lines (ie ALT + Enter) and Cell C2 would be how I would like it ordered - reverse of Cell B2.

Cell A3=AA , Cell B3= Comment 1,2,3 seperated by lines (ie ALT + Enter) and Cell C3 would be how I would like it ordered - reverse of Cell B3.

Cell A4=ZZ , Cell B4= Comment 1,2,3,4 seperated by lines (ie ALT + Enter) and Cell C4 is how I would like it ordered - reverse of Cell B4.

...and so on...


Hope this helps.

Thanks for looking, much appreciated!!!

ShuStar

 

[svendiamond]

I don't think you could have explained that in a more complicated way.

Cell B2 = Comment 1, 2, 3, 4 and 5? Separated by lines? You mean an empty row in between each comment?

Do yourself a favor and get the Mr Excel HTML Maker from this link: http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

Install and use it to copy and paste your data into the forum. It will make it much easier to read. Here's what I thought your data looked like (I'm using the Mr Excel HTML Maker for this

Book1
	A	B
1	Product ID	Comments Column Currently
2	QQ	Comment 1
3		Comment 2
4		Comment 3
5		Comment 4
6		Comment 5
7
8	AA	Comment 1
9		Comment 2
10		Comment 3
11
12	ZZ	Comment 1
13		Comment 2
14		Comment 3
15		Comment 4
16
17	WW	Comment 1
18		Comment 2
Sheet2

So, if it does indeed look like that, I have a solution for you:

Sub sortComments()


Dim lastRow As Long, myLoop As Long
Dim firstComment As Range, nextBlank As Range, rangeToSort As Range
Dim separateRanges As Long, firstBlank As Range

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    separateRanges = WorksheetFunction.CountIf(Range("B2:B" & lastRow), "") + 1
    Set nextBlank = Range("B2:B" & lastRow).Find("", , , , xlByRows, xlNext)
    Set firstComment = Cells(2, 2)
    Set firstBlank = nextBlank
        Debug.Print nextBlank.Address
        Debug.Print firstComment.Address

Do
    Set rangeToSort = Range(Cells(firstComment.Row, 2), Cells(nextBlank.Row - 1, 2))
        rangeToSort.Sort Range("B" & firstComment.Row), xlDescending
        
        Set firstComment = nextBlank.Offset(1)
        Set nextBlank = Range("B:B").FindNext(nextBlank)

        Debug.Print nextBlank.Address
        Debug.Print firstComment.Address
Loop While nextBlank.Address <> firstBlank.Address

End Sub

Otherwise, use the HTML Maker to show us what your data actually looks like. Thanks.

 

[ShuStar]

I don't think you could have explained that in a more complicated way.

Thanks.

Lol really sorry.... thanks for the tip, have DL MrExcelHTML....

The data looks like the below:

Excel 2012

	A	B	C
QQ	Comments 1 Comments 2 Comments 3 Comments 4 Comments 5	Comments 5 Comments 4 Comments 3 Comments 2 Comments 1
AA	Comment 1 Comment 2 Comment 3	Comment 3 Comment 2 Comment 1
ZZ	Comment 1 Comment 2 Comment 3 Comment 4	Comment 4 Comment 3 Comment 2 Comment 1
WW	Comment 1 Comment 2	Comment 2 Comment 1

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #4472C4"]Product ID[/TD]
[TD="bgcolor: #4472C4"]Comments Column Currently (how the data looks when exported)[/TD]
[TD="bgcolor: #4472C4"]Ideal Comments Layout[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</tbody>

Sheet1​

Hope this helps.

 

[svendiamond]

Oh... so "Comment1, Comment2, Comment3, Comment4, Comment5" is all in one cell??? Cell B2, that is?

 

[Rick Rothstein]

Give this macro a try...

Sub ReverseCommentsInCell()
  Dim R As Long, X As Long, Data As Variant, Temp As String, Parts() As String
  Data = Range("B2", Cells(Rows.Count, "B").End(xlUp))
  For R = 1 To UBound(Data)
    Temp = ""
    Parts = Split(Data(R, 1), vbLf)
    For X = UBound(Parts) To 0 Step -1
      Temp = Temp & vbLf & Parts(X)
    Next
    Data(R, 1) = Mid(Temp, 2)
  Next
  Range("B2").Resize(UBound(Data)) = Data
End Sub

 

[ShuStar]

Yes!!! That's right... All in one cell... In my example posted in this thread it starts from B2, but in the actual data extract it starts from column K2...

Any resolution you may have would be greatly appreciated!!!

 

[Rick Rothstein]

Yes!!! That's right... All in one cell... In my example posted in this thread it starts from B2, but in the actual data extract it starts from column K2...

Then why did you tell us it starts in B2? I'll never understand why posters insist on "simplifying" their problems for us... it almost always leads to a solution that they have to come back to the forum with and ask to be modified to fit their actual conditions. In the future... just tell us your actual layout next time. While I think you could figure out how to modify the code I posted in Message #7, here it is modified to work in Column K instead of Column B...

Sub ReverseCommentsInCell()
  Dim R As Long, X As Long, Data As Variant, Temp As String, Parts() As String
  Data = Range("K2", Cells(Rows.Count, "K").End(xlUp))
  For R = 1 To UBound(Data)
    Temp = ""
    Parts = Split(Data(R, 1), vbLf)
    For X = UBound(Parts) To 0 Step -1
      Temp = Temp & vbLf & Parts(X)
    Next
    Data(R, 1) = Mid(Temp, 2)
  Next
  Range("K2").Resize(UBound(Data)) = Data
End Sub

 

[narramist]

Hi ShuStar,


Try this, based on comments in Column K,

Sub ReverseComments()


Dim i As Long
Dim Comments As Variant
Dim FinalComments As String


For i = 2 To ActiveSheet.Cells.SpecialCells(xlLastCell).Row
    Comments = Split(Cells(i, 11).Value, Chr(13)) 'if not working, change Chr(13) to Chr(10)
        For j = 0 To UBound(Comments)
        FinalComments = Comments(j) & Chr(13) & FinalComments 'if not working, change Chr(13) to Chr(10)
        Next j
    ActiveSheet.Cells(i,11).Value = FinalComments
    FinalComments = ""
Next i


End Sub

The only thing to note is that depending on which linebreak character you've entered, you may need to refer to Chr(10) or Chr(13) as the linebreak character for the macro to look for. I've written the code presuming you're using Chr(13) (that's what worked on my sample) but if you're not seeing the result you want, there are two places in the code (that I've indicated in comments) where you'll want to change "13" for "10" and see if that works.