How to VBA sort by entire number instead of only first digit (numbers and text share cell space)

[Sleeplol]

Hello,

I'm hoping there is a quick fix to this only sorting by the first digit of the number

Sorting column BB

Sub DashTop_Descending()
Range("BB1:BB200").Sort _
Key1:=Range("BB1"), Order1:=xlDescending
End Sub

Works when using solely numbers but my columns looks like the below and ranks only the first digit (e.g. putting 15 & 12 below 2).
How can this sorting accommodate for the full number?

Thanks for any help with this.

 

[Joe4]

You can use helper column where you pull off the number to sort by, i.e.

=LEFT(BB2,FIND("-",BB2)-2)+0

Or, if you have the ability to change your data entry method, make it so that all your numbers are the same length, i.e.
01
02
03
...
09
10
11
...

 

[Sleeplol]

You can use helper column where you pull off the number to sort by, i.e.

=LEFT(BB2,FIND("-",BB2)-2)+0

Or, if you have the ability to change your data entry method, make it so that all your numbers are the same length, i.e.
01
02
03
...
09
10
11
...

Hi Joe4,
Ok, using your idea with the helper column. The numbers originate from countif formulas that were later aggregated with the "Topics".
This works just fine
Thanks

 

[Joe4]

You are welcome.

The numbers originate from countif formulas that were later aggregated with the "Topics"

Note, if that is the case, we may be able to update the formula being used to return two digit numbers, like I showed at the end of my previous post, so that you would not need a helper column.

If you are interested in looking in to that, please post the formula that is being used to combine the number with the "Topics".

 

[Sleeplol]

You are welcome.


Note, if that is the case, we may be able to update the formula being used to return two digit numbers, like I showed at the end of my previous post, so that you would not need a helper column.

If you are interested in looking in to that, please post the formula that is being used to combine the number with the "Topics".

Well, the helper columns are working, but I'll kick myself in the butt for not taking you up on teaching me a new technique, so...

Because of the update the columns have changed a bit; current layout

(Column AP) COUNTIF(C6:$C$20000,Y1)
(Column AQ) AP1&" - "&Y1

Where column Y has a unduplicated list of names that match the names in column C

I am curious how you would return 2 digit numbers in this case.


Thanks

 

[Joe4]

You would change the formula in column AQ from:

=AP1&" - "&Y1

to

=TEXT(AP1,"00") &" - "&Y1

 

[Sleeplol]

Oh that's cool. And more uniform. Yeah, I like this look more
Thanks

 

[Joe4]

You are welcome!