Tricky Sort solution sought

[mikemcbain]

G'day Magicians

Everyday I have extensive columns of info like the below...

I need to Sort it on the Form column so that the first rows contain the blank cells, followed by rows which contain any cells finishing with an x, followed by rows with cells finishing in x and one number, then x and two numbers, and then x and three numbers and finally four numbers.
Any suggestions gratefully considered.

Mike
Tasmania

RNO	 TNO	 RDST   FORM
7	13	1100	  16x
2	12	1100	  2x0
2	11	1100	  4x29
5	3	1400	  37x3
3	1	1100	  x223
2	8	1100	
3	7	1100	  0x34
2	5	1100	  1x
8	3	1100	  376x
3	9	1100	  16x
4	7	1400	  14x4
3	6	1100	  241x
2	14	1100	
1	5	1400	  0x68
7	3	1100	  1381

 

[VBA Geek]

you can add a helper column, then sort by that column


D2 FORMULA IS: =IF(C2="",0,LEN(C2)-SEARCH("x",C2)+1) and drag down

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	RNO	TNO	RDSTFORM	srt col				RNO	TNO	RDSTFORM	srt col
2	7 13	1100	16x	1				2 8			0
3	2 12	1100	2x0	2				2 14			0
4	2 11	1100	4x29	3				7 13	1100	16x	1
5	5 3	1400	37x3	2				2 5	1100	1x	1
6	3 1	1100	1x223	4				8 3	1100	376x	1
7	2 8			0				3 9	1100	16x	1
8	3 7	1100	80x34	3				3 6	1100	241x	1
9	2 5	1100	1x	1				2 12	1100	2x0	2
10	8 3	1100	376x	1				5 3	1400	37x3	2
11	3 9	1100	16x	1				4 7	1400	714x4	2
12	4 7	1400	714x4	2				2 11	1100	4x29	3
13	3 6	1100	241x	1				3 7	1100	80x34	3
14	2 14			0				1 5	1400	00x68	3
15	1 5	1400	00x68	3				3 1	1100	1x223	4
16	7 3	1100	55x345687	7				7 3	1100	55x345687	7
Sheet4

 

[njimack]

If you're happy using a helper column, paste the below into a spare column and then sort on that column...
=IFERROR(IF(LEN(D2)=0,1,IF(RIGHT(D2)="x",2,2+LEN(D2)-FIND("x",D2))),6)

 

[wesleyferns]

Hi Mike,

Would you be open to adding an extra column with a formula to something similar below

=IFERROR(LEN(D2)-(FIND("x",D2)),-1)?

You could sort this column from smallest to largest.

 

[mikemcbain]

wesleyferns, njimack & VBA Geek

Perfect and thank you all very much!

I will trial each of your suggestions and expect them all to work perfectly for my purpose.

You really are magicians.

Mike
Tasmania.

 

[mikemcbain]

VBA GEEK

I have chosen to go with your solution however unfortunately I missed one example that we must cater for in my original post and that is the occasional cell with no x but just a single number like shown below.

RNO	TNO	RDSTFORM	srt col
7 13	1100	16x	1
2 12	1100	2x0	2
2 11	1100	4x29	3
5 3	1400	37x3	2
3 1	1100	1x223	4
2 8			0
3 7	1100	80x34	3
2 5	1100	4	[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE[/URL] !
8 3	1100	376x	1
3 9	1100	16x	1
4 7	1400	714x4	2
3 6	1100	241x	1
2 14			0
1 5	1400	00x68	3
7 3	1100	55x345687	7

I have tried to find a solution but am sure you can come up with something simple?

With thanks

Mike.
Tasmania

 

[VBA Geek]

How should this be ranked?

VBA GEEK

I have chosen to go with your solution however unfortunately I missed one example that we must cater for in my original post and that is the occasional cell with no x but just a single number like shown below.

RNO    TNO    RDSTFORM    srt col
7 13    1100    16x    1
2 12    1100    2x0    2
2 11    1100    4x29    3
5 3    1400    37x3    2
3 1    1100    1x223    4
2 8            0
3 7    1100    80x34    3
2 5    1100    4    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE"]#VALUE[/URL] !
8 3    1100    376x    1
3 9    1100    16x    1
4 7    1400    714x4    2
3 6    1100    241x    1
2 14            0
1 5    1400    00x68    3
7 3    1100    55x345687    7

I have tried to find a solution but am sure you can come up with something simple?

With thanks

Mike.
Tasmania

 

[mikemcbain]

I would prefer this to be ranked immediately after the Blank cell and before the x? cell but for simplicity the same ranking as x? would be fine.

 

[VBA Geek]

I would prefer this to be ranked immediately after the Blank cell and before the x? cell but for simplicity the same ranking as x? would be fine.

you can update the formula in D2 to

=IF(C2="",0,IF(ISERROR(SEARCH("x",C2)),1,LEN(C2)-IFERROR(SEARCH("x",C2),LEN(C2))+2))

 

[mikemcbain]

VBA GEEK

Magic stuff! Thank you sincerely.

Mike.