Sorting horizontally

[tigerzen]

Wish to sort the first table so that it produces the second table. I use the MID function to extract the number with which to sort however when I try and sort, it puts Alpha10 in front of Alpha2. I can get this to work if I manually enter the sort numbers, what can I do to the MID function so that its length is one character only rather than the 2 which is what I think is throwing this off. There could well be a better way to handle this sort so any suggestions appreciated.

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	Name	P1 Alpha	P10 Alpha	P12 Alpha	P2 Alpha		Name	P1 Alpha	P2 Alpha	P10 Alpha	P12 Alpha
2	Tom	3	3	8	9		Tom	3	9	3	8
3	Jack	2	4	9	10		Jack	2	10	4	9
4	Bert	9	7	4	2		Bert	9	2	7	4
5	Ken	3	1	10	1		Ken	3	1	1	10
6	To Sort by	1	10	12	2		To Sort by	1	2	10	12
Sheet1

Cell Formulas
Range	Formula
B6:E6	B6	=MID(B1,2,2)

 

[kevin9999]

You could do this easily with a custom sort, something like this perhaps:

Option Explicit
Sub Custom_Sort()
    Dim ws As Worksheet, Rng As Range
    Set ws = Worksheets("Sheet1")       '<-- *** Change to actual sheet name ***
    Set Rng = ws.Range("B1", Cells(Rows.Count, "E").End(xlUp))
    
    Application.AddCustomList ListArray:=Array("P1 Alpha", "P2 Alpha", "P10 Alpha", "P12 Alpha")
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range(ws.Cells(1, 2), ws.Cells(1, 5)), _
        CustomOrder:=Application.CustomListCount
        .SetRange Rng
        .Orientation = xlLeftToRight
        .Apply
    End With
End Sub

 

[mehidy1437]

Also look into this;
Sorting Columns Instead of Rows in Excel

 

[MARK858]

. I use the MID function to extract the number with which to sort however when I try and sort, it puts Alpha10 in front of Alpha2

Did you get the box below come up when you tried the sort? and did you choose to sort text as a number?

or could you just amend your formula to

=--MID(B1,2,2)

 

[tigerzen]

Did you get the box below come up when you tried the sort? and did you choose to sort text as a number?
View attachment 97871

or could you just amend your formula to

=--MID(B1,2,2)

Hi Mark, I did get that dialog box and I'm certain that I clicked on the first option but it did not produce the desired result. The original file contained hundreds of columns and unbeknownst to me, some of the helper cells were considered text, no rhyme or reason as to why this was the case. Anyway, thanks for your solution it works well, how does the -- in front of the MID function impact upon it, does it change into a value?

 

[MARK858]

how does the -- in front of the MID function impact upon it, does it change into a value?

It just converts text numbers to real numbers, it also converts True and False to 1 and 0 respectively so you can use them in calculations.
You could use any arithmetic operator to do the converting to number i.e.

=MID(B1,2,2)+0

and

=MID(B1,2,2)*1

would also work

 

[tigerzen]

It just converts text numbers to real numbers, it also converts True and False to 1 and 0 respectively so you can use them in calculations.
You could use any arithmetic operator to do the converting to number i.e.

=MID(B1,2,2)+0

and

=MID(B1,2,2)*1

would also work

Thanks for the explanation.

 

[MARK858]

You're welcome