Ask Your Question
1

Ignore first several characters in sort?

asked 2017-07-14 07:21:21 +0100

Nilesandstuff gravatar image

I've searched really hard, i honestly just dont know how to word this...

I need to sort two different columns so that they match their partner (luckily Im starting with the two columns in seperate documents and then combine them after sorting, which so far seems like it allows for more flexibilty)

Basically, my starting set looks like this:

57238-BLUEBERRY012.pdf  BLUEBERRY0MG120ML
57239-BLUEBERRY055.pdf  BLUEBERRY0MG12ML
57240-BLUEBERRY0120.pdf BLUEBERRY0MG32ML
57241-BLUEBERRY032.pdf  BLUEBERRY0MG55ML
57242-BLUEBERRY312.pdf  BLUEBERRY12MG120ML
57243-BLUEBERRY332.pdf  BLUEBERRY12MG12ML
57244-BLUEBERRY355.pdf  BLUEBERRY12MG32ML
57245-BLUEBERRY612.pdf  BLUEBERRY12MG55ML
57246-BLUEBERRY632.pdf  BLUEBERRY18MG120ML
57247-BLUEBERRY655.pdf  BLUEBERRY18MG12ML
57248-BLUEBERRY1212.pdf BLUEBERRY18MG32ML
57249-BLUEBERRY1232.pdf BLUEBERRY18MG55ML
57250-BLUEBERRY1255.pdf BLUEBERRY24MG120ML
57251-BLUEBERRY1855.pdf BLUEBERRY24MG12ML
57252-BLUEBERRY1832.pdf BLUEBERRY24MG32ML
57253-BLUEBERRY2412.pdf BLUEBERRY24MG55ML
57254-BLUEBERRY1812.pdf BLUEBERRY36MG120ML
57255-BLUEBERRY2432.pdf BLUEBERRY36MG12ML
57256-BLUEBERRY2455.pdf BLUEBERRY36MG32ML
57257-BLUEBERRY3120.pdf BLUEBERRY36MG55ML
57258-BLUEBERRY3612.pdf BLUEBERRY3MG120ML
57259-BLUEBERRY3632.pdf BLUEBERRY3MG12ML
57260-BLUEBERRY3655.pdf BLUEBERRY3MG32ML
57261-BLUEBERRY6120.pdf BLUEBERRY3MG55ML
57262-BLUEBERRY12120.pdf    BLUEBERRY6MG120ML
57263-BLUEBERRY18120.pdf    BLUEBERRY6MG12ML
57264-BLUEBERRY24120.pdf    BLUEBERRY6MG32ML
57265-BLUEBERRY36120.pdf    BLUEBERRY6MG55ML

And all i need is for them to match up to their mate like this:

57238-BLUEBERRY012.pdf  BLUEBERRY0MG12ML

Neither column has to actually be in a particular order, they just have to line up with their partner.

Is it possible to create a custom sort that would ignore the first 5 numbers and dashes in column 1?

OR Is it possible to sort column 2 in a way that matches their partners in column 1 (possibly with custom sort?)

important notes: Column 1 can NOT be modified in even the slightest way... however, column 2 can be modified in literally ANY way. For example, one of the cells in column 2 could say "0120" instead of "BLUEBERRY0MG120ML"... as long as those changes can be done with search and replace... Ive got over 200 of these sets.

(i've left out a detail in order to simplify this question... the data in column 2 doesnt actually matter... there is a third column thats matched with column 2 that contains the important data, but I can just extend the range to include that column easily... which is why I left this detail out from my question. The only reason I explained that here is so that you can know I REALLY dont care what is done to modify column 2)

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2017-07-15 04:40:00 +0100

librebel gravatar image

Hello @Nilesandstuff,

Responding to the question asked in your comment to the answer provided by @Lupp, to automatically create a helper column with search and replace, you could use the methods CopyRange() and ReplacePattern() :

Sub CopyRange( strSourceRange As String, strTargetCell As String )
REM Copies the specified Source Range into a new Range that starts from the specified Target Cell.
REM <strSourceRange>    : specifies the Range to copy, e.g. "A1:B99".
REM <strTargetCell>     : specifies the starting Cell to put the result in, e.g. "D1". Must be a single cell.
    Dim oSheet As Object, oSourceRangeAddress As Object, oTargetCellAddress as Object
    oSheet = ThisComponent.CurrentController.ActiveSheet
    oSourceRangeAddress = oSheet.getCellRangebyName( strSourceRange ).getRangeAddress()
    oTargetCellAddress = oSheet.getCellRangebyName( strTargetCell ).CellAddress
    oSheet.copyRange( oTargetCellAddress, oSourceRangeAddress )
End Sub

Function ReplacePattern( strSourceRange As String, strPatternToFind As String, strPatternToReplace As String ) As Long
REM Replaces all occurrences of <strPatternToFind> with <strPatternToReplace>, within the specified CellRange.
REM <strSourceRange>:   e.g. "A1:A9999".
REM Returns the number of found/replaced rows.
    Dim oSheet As Object, oSource As Object, oReplace as Object
    oSheet = ThisComponent.CurrentController.ActiveSheet
    oSource = oSheet.getCellRangebyName( strSourceRange )
    oReplace = oSource.createReplaceDescriptor()
    oReplace.setSearchString( strPatternToFind )
    oReplace.setReplaceString( strPatternToReplace )
    oReplace.SearchType = 1     REM 0=search formulas; 1=search values; 2=search notes. [Calc only] 
    oReplace.SearchRegularExpression = True
    oReplace.SearchByRow = True
    ReplacePattern = oSource.replaceAll( oReplace )
End Function

For example suppose your columns are A and B, then you could automatically insert the helper columns in C and D respectively:

CopyRange( "A1:A999", "C1" )            REM  999 is the number of the last row to copy.
ReplacePattern( "C1:C999", ".*-", "" )

CopyRange( "B1:B999", "D1" )            REM  999 is the number of the last row to copy.
ReplacePattern( "D1:D999", "MG","" )
ReplacePattern( "D1:D999", "ML", "" )

Then you could Sort column C (Extended) and with "Options : Enable natural sort", then column D. The sorting could also be automated.

edit flag offensive delete link more
1

answered 2017-07-14 10:21:15 +0100

Lupp gravatar image

updated 2017-07-14 21:59:12 +0100

(Quoted from OQ:) Is it possible to create a custom sort that would ignore the first 5 numbers and dashes in column 1?

No. At least not without custom programming ("macos").

(Quoted from OQ:) Is it possible to sort column 2 in a way that matches their partners in column 1 (possibly with custom sort?)

No. (See above.)

(Quoted from OQ:) Column 1 can NOT be modified in even the slightest way... however, column 2 can be modified in literally ANY way.

We should never modify original data, exept for cases where the modification itself is what we want to achieve.

What to do then?
Use a helper column. It must be "in contact" with the first column and (probably) its accompanying data.

See this attached example.

(Editing with respect to th comment below:)
I only remember one helper column labeled "SORTINGCOMPANION1_HELPER" and introduced in sheet "OriginalDataCompanion1Added". The reslults shown there are returned by the formulas contained in the cells. Disregarding the amount of interactive handling and the error risks coming with that, you might also do it with 'F&R' applied to a copy of the original data. I prefer the formulas in such a case.
New demo attached.

edit flag offensive delete link more

Comments

oh i see! Im glad you knew what I meant about how i was using column 2, I just didnt know the term helper column!

So what I glean from your response is the best way would be to make a helper column for column 1(like you did), and I could use search + replace to make helper column 2 match the format of the helper column 1. looking like:

Helper column 1     Helper column 2
BLUEBERRY012.pdf  BLUEBERRY012

My only question is how did you create helpercolumn 2? Search and replace? How?

Nilesandstuff gravatar imageNilesandstuff ( 2017-07-14 21:25:25 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-07-14 07:21:21 +0100

Seen: 109 times

Last updated: Jul 15 '17