How to sort groups of numbers in rows to interleaved columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1525470%22%20slang%3D%22en-US%22%3EHow%20to%20sort%20groups%20of%20numbers%20in%20rows%20to%20interleaved%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525470%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Folks%3C%2FP%3E%3CP%3EThis%20has%20had%20me%20wracking%20my%20brain%20for%20a%20couple%20of%20days%2C%20so%20any%20suggestions%20hugely%20appreciated!%3C%2FP%3E%3CP%3EI%20have%20a%20sheet%20that%20looks%20in%20part%20like%20the%20following%3CSPAN%3E%20%3CFONT%20color%3D%22%23000000%22%3Ethere's%20around%202000%20rows%2C%20so%20looking%20a%20VBA%20snippet%20unless%20there's%20a%20funky%20sort%20I've%20not%20found%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FFONT%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSPAN%3E1%3C%2FSPAN%3E%3C%2FFONT%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSPAN%3E1%3C%2FSPAN%3E%3C%2FFONT%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23339966%22%3E%3CSPAN%3E2%3C%2FSPAN%3E%3C%2FFONT%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSPAN%3E3%3C%2FSPAN%3E%3C%2FFONT%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSPAN%3E3%3C%2FSPAN%3E%3C%2FFONT%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF6600%22%3E%3CSPAN%3E4%3C%2FSPAN%3E%3C%2FFONT%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF6600%22%3E%3CSPAN%3E4%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eand%20I'm%20after%20it%20looking%20like%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CFONT%20color%3D%22%23FF0000%22%3E1%3C%2FFONT%3E%3CFONT%20color%3D%22%23339966%22%3E2%3C%2FFONT%3E%3CFONT%20color%3D%22%230000FF%22%3E3%3C%2FFONT%3E%3CFONT%20color%3D%22%23FF6600%22%3E4%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CFONT%20color%3D%22%23FF0000%22%3E1%3C%2FFONT%3E%3CFONT%20color%3D%22%230000FF%22%3E3%3C%2FFONT%3E%3CFONT%20color%3D%22%23FF6600%22%3E4%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CFONT%20color%3D%22%23FF0000%22%3E1%3C%2FFONT%3E%3CFONT%20color%3D%22%23339966%22%3E2%3C%2FFONT%3E%3CFONT%20color%3D%22%230000FF%22%3E3%3C%2FFONT%3E%3CFONT%20color%3D%22%23FF6600%22%3E4%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3E%3CSPAN%3EThanks%20in%20advance!%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1525470%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1525531%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20sort%20groups%20of%20numbers%20in%20rows%20to%20interleaved%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525531%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F133955%22%20target%3D%22_blank%22%3E%40Adrian%20Watkins%3C%2FA%3E%26nbsp%3B%2C%20what's%20the%20logic%20of%20the%20grouping%3F%20I%20can't%20see%20a%20pattern%20just%20by%20looking%20at%20the%20example.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1525604%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20sort%20groups%20of%20numbers%20in%20rows%20to%20interleaved%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525604%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3Bthanks%20for%20the%20reply!%20sorry%2C%20it%E2%80%99s%20repeated%20groups%20of%20up%20to%201-4%20sorted%20numerically%2C%20so%20they%20are%20like%20111222333444%20but%20some%20groups%20may%20skip%20some%20numbers%2C%20so%20min%20of%20one%20number%2C%20max%20of%20four%20and%20results%20have%20to%20be%20in%20numeric%20order%201234%20with%20no%20gaps%20for%20skipped%20ones.%20Full%20job%20is%20combining%20a%20series%20of%20worksheets%20that%20were%20done%20by%20row%20when%20they%20should%20have%20been%20column%20as%20source%20for%20a%20mail%20merge%20%3Aconfounded_face%3A%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1526866%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20sort%20groups%20of%20numbers%20in%20rows%20to%20interleaved%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1526866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F133955%22%20target%3D%22_blank%22%3E%40Adrian%20Watkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20your%20version%20of%20Excel%20supports%20dynamic%20arrays%2C%20and%20if%20I%20understood%20the%20logic%20correctly%2C%20you%20may%20add%20helper%20column%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20552px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F205826i446E572BF6375560%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D--CONCAT(UNIQUE(MID(A1%2CSEQUENCE(1%2CLEN(A1))%2C1)%2C1))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20sort%20by%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi Folks

This has had me wracking my brain for a couple of days, so any suggestions hugely appreciated!

I have a sheet that looks in part like the following there's around 2000 rows, so looking a VBA snippet unless there's a funky sort I've not found

1 

1 

2  

3 

3 

4 

4 

and I'm after it looking like

1234

134

1234

 

Thanks in advance!

 

3 Replies

@Adrian Watkins , what's the logic of the grouping? I can't see a pattern just by looking at the example.

@TheAntony thanks for the reply! sorry, it’s repeated groups of up to 1-4 sorted numerically, so they are like 111222333444 but some groups may skip some numbers, so min of one number, max of four and results have to be in numeric order 1234 with no gaps for skipped ones. Full job is combining a series of worksheets that were done by row when they should have been column as source for a mail merge

@Adrian Watkins 

If your version of Excel supports dynamic arrays, and if I understood the logic correctly, you may add helper column

image.png

with formula

=--CONCAT(UNIQUE(MID(A1,SEQUENCE(1,LEN(A1)),1),1))

and sort by it.