Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Ignore first several characters in sort?

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)