PDA

View Full Version : sort rows based on criteria



austenr
12-12-2012, 12:37 PM
I need to be able to sort rows based on the data after the last / in that row.

Example:


321MADAV-NYBKO1/DATA:SHARED/ABL

austenr
12-12-2012, 12:48 PM
One step further, can you break out a column into individual rows that has wrapped entries in it if you know that each entry will end with .CBH?
The example is one cell.
Example:

"[ RWCEMF ] SHARE-321MADAV-LENDING.GRP.SRS.108QUEEN.QUEE.NY.CBH
[ RWCEMF ] SHARE-321MADAV-LENDING.GRP.SRS.1100LAKE.BERG.NJ.CBH
[ RWCEMF ] SHARE-321MADAV-LENDING.GRP.SRS.205MONT.BROK.NY.CBH
[ RWCEMF ] SHARE-321MADAV-LENDING.GRP.SRS.2WALLST.MANH.NY.CBH
[ RWCEMF ] SHARE-321MADAV-LENDING.GRP.SRS.321MADAV.MANH.NY.CBH
[ RWCEMF ] SHARE-321MADAV-LENDING.GRP.SRS.500SHRWS.MONM.NJ.CBH
[ RWCEMF ] SHARE-321MADAV-LENDING.GRP.SRS.560RT22.SOME.NJ.CBH
[ RWCEMF ] SHARE-321MADAV-LENDING.GRP.SRS.MELVILLE-324SSERVICE.SUFF.NY.CBH
[ RWCEMF ] SHARE-NY-321MADAV-LENDING .GRP.SRS.WILTON-40DANBURY.FAIR.CT.CBH
[ RWCEMF ] SHARE-NY-321MADAV-LENDING.GRP.SRS.475PARK.MANH.NY.CBH
[ RWCEMF ] SHARE-NY-321MADAV-LENDING.GRP.SRS.535ECRES.BERG.NJ.CBH
[ RWCEMF ] SHARE-NY-321MADAV-LENDING.GRP.SRS.NEWYORK-444MADISON.MANH.NY.CBH"

CodeNinja
12-12-2012, 03:35 PM
I need to be able to sort rows based on the data after the last / in that row.

Example:


321MADAV-NYBKO1/DATA:SHARED/ABL


Use a helper column with the following formula then sort to that colum:
=RIGHT(A1,LEN(A1) - FIND("^^",SUBSTITUTE(A1,"/","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

CodeNinja
12-12-2012, 03:58 PM
Second issue, a vba solution would be something like:

Sub spread()
Dim l As Long
Dim str As String
Dim ioffset As Integer

For l = 1 To 5
ioffset = 2
str = Sheet1.Cells(l, 1)
While InStr(1, str, ".CBH")
Sheet1.Cells(l, ioffset) = Left(str, InStr(1, str, ".CBH") + 4)
ioffset = ioffset + 1
str = Trim(str) 'get rid of spaces
str = Mid(str, InStr(1, str, ".CBH") + 5) 'get rid of first line plus line feed
Wend
Next l
End Sub
I'll have to think of a solution for a formula based answer... so if that is what you want, reply and let me know ... I'll think on it.

p45cal
12-13-2012, 05:51 AM
One step further, can you break out a column into individual rows that has wrapped entries in it if you know that each entry will end with .CBH?
The example is one cell.

You could also try the following which takes the long string in A1 and splits it out into rows starting at A2.
But.. I'd guess that the single long string is actually already separated by a linefeed, carriage return or some such, and it might be simpler to split the string using that character.Sub blah()
y = Split(Range("A1").Value, "CBH")
For i = 0 To UBound(y) - 1
y(i) = y(i) & "CBH"
Next i
Range("A2").Resize(UBound(y)) = Application.Transpose(y)
End Sub


If it is a linefeed character then the macro could be quite simple:Sub blah2()
y = Split(Range("A1").Value, vbLf)
Range("A2").Resize(UBound(y) + 1) = Application.Transpose(y)
End Sub

austenr
01-07-2013, 08:30 AM
OK I cant get the macro solution to work. I am attaching a sample WB. What I want is to break out each cells with a separate line after .cbh.

CodeNinja
01-07-2013, 09:20 AM
austenr,
Not 100% sure what your goal is, but I think the following code may accomplish it... What it does is create a column B with each line item split by .CBH... You could also use vblf as your delimiter and not have to deal with putting .CBH back in... but if you use vbcrlf in any of those you would have issues...
Anyway, if this solves your problem... great. If not, please clarify what exactly you want it to look like when done.

Thanks,

CodeNinja

Sub spread()
Dim iRowNew As Integer
Dim iRowExisting As Integer
Dim iArray As Integer
Dim s() As String

iRowNew = 1

For iRowExisting = 1 To Sheet1.Range("A65536").End(xlUp).Row
s() = Split(Sheet1.Cells(iRowExisting, 1), ".CBH")
For iArray = LBound(s) To UBound(s) - 1
Sheet1.Cells(iRowNew + iArray, 2) = s(iArray) & ".CBH"
Next iArray
iRowNew = iRowNew + UBound(s)

Next iRowExisting

End Sub

austenr
01-07-2013, 09:46 AM
Nope that didnt work. Take a look at A2 in the sample WB. What I need ideally is to have 4 separate rows from that cell and each row from A2 should be delimited by .cbh.

Tried running your macro and it doesn't produce anything. A new sheet would be nice. I've got hundreds of these kinds of cells and I really need to sort them and cant do it the way they are now.

p45cal
01-07-2013, 01:11 PM
trySub blah2()
For Each cll In Sheets("Sheet1").Range("A1").CurrentRegion
y = Split(cll.Value, vbLf)
Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(UBound(y) + 1) = Application.Transpose(y)
Next cll
End Sub
results are on sheet2.