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

ShuStar

Board Regular
Joined
Sep 9, 2015
Messages
89
Office Version
  1. 2010
Platform
  1. Windows
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!!!
Product ID
Comments Column Currently
Ideal Comment Layout
QQ
Comment 1
Comment 2
Comment 3
Comment 4
Comment 5

Comment 5
Comment 4
Comment 3
Comment 2
Comment 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


<tbody>
</tbody>
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 9, 2015
Messages
89
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows
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:)

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Product ID</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Comments Column Currently</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #B22222;;">QQ</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #B22222;;">Comment 1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #B22222;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #B22222;;">Comment 2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #B22222;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #B22222;;">Comment 3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #B22222;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #B22222;;">Comment 4</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #B22222;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #B22222;;">Comment 5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #B22222;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #B22222;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #8B4513;;">AA</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #8B4513;;">Comment 1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #8B4513;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #8B4513;;">Comment 2</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #8B4513;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #8B4513;;">Comment 3</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #8B4513;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #8B4513;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #2F4F4F;;">ZZ</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #2F4F4F;;">Comment 1</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #2F4F4F;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #2F4F4F;;">Comment 2</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #2F4F4F;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #2F4F4F;;">Comment 3</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #2F4F4F;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #2F4F4F;;">Comment 4</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #2F4F4F;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #2F4F4F;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FF8C00;;">WW</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FF8C00;;">Comment 1</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FF8C00;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FF8C00;;">Comment 2</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br />



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

Code:
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.
 
Last edited:

ShuStar

Board Regular
Joined
Sep 9, 2015
Messages
89
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
ABC
1Product IDComments Column Currently (how the data looks when exported)Ideal Comments Layout
2QQComments 1
Comments 2
Comments 3
Comments 4
Comments 5
Comments 5
Comments 4
Comments 3
Comments 2
Comments 1
3AAComment 1
Comment 2
Comment 3
Comment 3
Comment 2
Comment 1
4ZZComment 1
Comment 2
Comment 3
Comment 4
Comment 4
Comment 3
Comment 2
Comment 1
5WWComment 1
Comment 2
Comment 2
Comment 1

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Hope this helps.
 

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows
Oh... so "Comment1, Comment2, Comment3, Comment4, Comment5" is all in one cell??? Cell B2, that is?
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Give this macro a try...
Code:
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

Board Regular
Joined
Sep 9, 2015
Messages
89
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
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...
Code:
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

New Member
Joined
Jan 28, 2014
Messages
14
Hi ShuStar,


Try this, based on comments in Column K,
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,353
Messages
5,595,668
Members
414,007
Latest member
bongda360org

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top