Ignore first several characters in sort?

[Nilesandstuff]

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)

[Lupp]

(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.

[Nilesandstuff]

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?

[librebel]

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.