macro need quite complicated data sorting

L

leecavturbo

Well-known Member
Joined
Jan 4, 2008
Messages
681
here i have results from a race. the racers have numbers in the format of a20,c51 etc
then the name of the competitor and car make model.
then some times that they run. in the format of 11.80 secs the following times have either thousands or hundredthousths of a second which i only want to comma seperate the 2 decimals before and after so a 12.3456 will be ,12.34,ignoring the 56 then the next result and so on.
so i want to comma seperate the a20,andrewspeck,vauxhalltigra,11.27,13.36,12.87,12.91 etc etc
now the name and vehicle will cause huge sorting head ache so leave those together i can seperate them manually.
TIA
.
A7,Andrew Speck,Vauxhall Tigra,11.27,13.36116,12.87113,12.91124,12.14134,11.37129,11.27128
2A4Iain BurtVauxhall Astra11.8012.1011311.8811411.8211511.80120
A2Chris SuttonVauxhall Corsa11.9514.1911413.8811212.4612412.1511511.9511814.08112
A17Andy NichollsRover 200 Turbo12.0812.0811612.8211712.50119
1A4Iain BurtVauxhall Astra12.0812.0811212.5812312.4611612.5311712.2910512.18111
A4Iain BurtVauxhall Astra12.1313.5811512.6111712.1811012.4811512.1311112.29113
1A8Gareth BirchVauxhall Nova12.1412.1411612.769912.18119
A8Gareth BirchVauxhall Nova12.1912.9811412.3811012.4111513.009612.4611212.19114
1A7Andrew SpeckVauxhall Tigra12.2612.26131
A10Errol HuellHonda CRX Turbo12.3613.3211513.2912315.726912.3610822.3652
1A14Westan FrickerFiat Coupe Turbo12.7812.78110
A14Westan FrickerFiat Coupe Turbo12.8414.8411513.7411413.4011513.3011513.0511312.84109
A19Richard CooperRER Seat Ibiza12.9814.1711915.4510813.9710813.7111014.4010512.98119
A18Paul BargateVW Scirroco13.0613.5810437.128824.012313.06103
1A13Leighton WilsonFiat Coupe Turbo13.1413.1411713.798513.4811413.17116
A13Leighton WilsonFiat Coupe Turbo13.1414.1911314.0711316.1410713.3211513.1411713.47115
A12Nigel OgramFiat Coupe Turbo13.1614.0111014.4411114.1010813.16116
A16Mark CallisterEurospec Mitsubishi FTO13.2415.7910313.2410613.389627.084214.679415.8276
1A1David WilkinsonVauxhall Corsa13.3513.35105
A15Richard BattyMitsubishi FTO13.3614.7410713.36129
1A16Mark CallisterEurospec Mitsubishi FTO13.4714.348914.1110413.4711014.16108
A1David WilkinsonVauxhall Corsa13.5013.5110213.7710513.5910513.5010513.6210613.68102
A11Nick JonesTD Toyota Starlet13.5614.899115.929313.56105
A3Gavin WorkerVauxhall Corsa13.6214.7210913.6211014.3011616.236813.71115
1A19Richard CooperRER Seat Ibiza13.8114.359913.81109
 
Sort by date Sort by votes

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Excel Workbook
ABCDEFGHI
1Class A - Front Wheel Drive
2A7,Andrew Speck,Vauxhall Tigra,11.27,13.36116,12.87113,12.91124,12.14134,11.37129,11.27128
32A4Iain BurtVauxhall Astra11.8012.1011311.8811411.8211511.80120
4A2Chris SuttonVauxhall Corsa11.9514.1911413.8811212.4612412.1511511.9511814.08112
5A17Andy NichollsRover 200 Turbo12.0812.0811612.8211712.50119
61A4Iain BurtVauxhall Astra12.0812.0811212.5812312.4611612.5311712.2910512.18111
7A4Iain BurtVauxhall Astra12.1313.5811512.6111712.1811012.4811512.1311112.29113
81A8Gareth BirchVauxhall Nova12.1412.1411612.769912.18119
9A8Gareth BirchVauxhall Nova12.1912.9811412.3811012.4111513.009612.4611212.19114
101A7Andrew SpeckVauxhall Tigra12.2612.26131
11A10Errol HuellHonda CRX Turbo12.3613.3211513.2912315.726912.3610822.3652
121A14Westan FrickerFiat Coupe Turbo12.7812.78110
13A14Westan FrickerFiat Coupe Turbo12.8414.8411513.7411413.4011513.3011513.0511312.84109
14A19Richard CooperRER Seat Ibiza12.9814.1711915.4510813.9710813.7111014.4010512.98119
15A18Paul BargateVW Scirroco13.0613.5810437.128824.012313.06103
161A13Leighton WilsonFiat Coupe Turbo13.1413.1411713.798513.4811413.17116
17A13Leighton WilsonFiat Coupe Turbo13.1414.1911314.0711316.1410713.3211513.1411713.47115
18A12Nigel OgramFiat Coupe Turbo13.1614.0111014.4411114.1010813.16116
19A16Mark CallisterEurospec Mitsubishi FTO13.2415.7910313.2410613.389627.084214.679415.8276
201A1David WilkinsonVauxhall Corsa13.3513.35105
21A15Richard BattyMitsubishi FTO13.3614.7410713.36129
221A16Mark CallisterEurospec Mitsubishi FTO13.4714.348914.1110413.4711014.16108
23A1David WilkinsonVauxhall Corsa13.5013.5110213.7710513.5910513.5010513.6210613.68102
24A11Nick JonesTD Toyota Starlet13.5614.899115.929313.56105
25A3Gavin WorkerVauxhall Corsa13.6214.7210913.6211014.3011616.236813.71115
261A19Richard CooperRER Seat Ibiza13.8114.359913.81109
27
28Class B - Rear Wheel Drive
29B2John WebsterMG SVR9.809.8016010.021449.94144
30B20Nathan FrekeToyota MR210.2510.6113612.888010.2513411.38125
31B37Nadine GearyDodge Viper10.7711.8013411.0713610.7713512.6913211.66135
32B3Stuart BlackeryWestfield Cosworth10.9211.4013211.4312310.9212511.2612711.1612511.11127
33B23Stephen RossFord Granada Twin Turbo11.2011.6313711.5411.8712111.3213111.2013811.34138
341B23Stephen RossFord Granada Twin Turbo11.2411.2413811.32137
35B36Carl BridsonDodge Viper11.4612.5411512.2812111.7112411.4612814.0113211.65132
36B1Duncan CowperDAX Rush Turbo11.4911.49126
371B36Carl BridsonDodge Viper11.5111.6810911.5113411.58128
381B16Larry VallotToyota MR211.6112.3511912.0812012.7611712.2311711.61121
39B24Paul NorrisLotus Sunbeam Cosworth11.7314.8112712.1112112.6912311.9912812.3612511.73127
40B6Martyn WillisVX Polo 1.8T11.8917.734613.568214.786911.8911614.0875
Sheet1
 
Upvote 0
If you can manage to get the providers of data to commas between the first few fields, this will be quite simple.

See what you can do. Without them, this is a bit of brainache.
 
Upvote 0
leecavturbo,

After the new macro:


Excel Workbook
ABCDEFGHIJK
1Class A - Front Wheel Drive
2A7,Andrew Speck,Vauxhall Tigra,11.27,13.36116,12.87113,12.91124,12.14134,11.37129,11.27128A7,Andrew Speck,Vauxhall Tigra,11.2713.3612.8712.9112.1411.3711.27
3A7Andrew SpeckVauxhallTigra11.2713.3611612.8711312.9112412.1413411.3712911.27128A7Andrew SpeckVauxhallTigra11.2713.3612.8712.9112.1411.3711.27
42A4Iain BurtVauxhall Astra11.8012.1011311.8811411.8211511.801202A4Iain BurtVauxhall Astra11.8012.1011.8811.8211.80
5A2Chris SuttonVauxhall Corsa11.9514.1911413.8811212.4612412.1511511.9511814.08112A2Chris SuttonVauxhall Corsa11.9514.1913.8812.4612.1511.9514.08
6A17Andy NichollsRover 200 Turbo12.0812.0811612.8211712.50119A17Andy NichollsRover 200 Turbo12.0812.0812.8212.50
71A4Iain BurtVauxhall Astra12.0812.0811212.5812312.4611612.5311712.2910512.181111A4Iain BurtVauxhall Astra12.0812.0812.5812.4612.5312.2912.18
8A4Iain BurtVauxhall Astra12.1313.5811512.6111712.1811012.4811512.1311112.29113A4Iain BurtVauxhall Astra12.1313.5812.6112.1812.4812.1312.29
91A8Gareth BirchVauxhall Nova12.1412.1411612.769912.181191A8Gareth BirchVauxhall Nova12.1412.1412.7612.18
10A8Gareth BirchVauxhall Nova12.1912.9811412.3811012.4111513.009612.4611212.19114A8Gareth BirchVauxhall Nova12.1912.9812.3812.4113.0012.4612.19
111A7Andrew SpeckVauxhall Tigra12.2612.261311A7Andrew SpeckVauxhall Tigra12.2612.26
12A10Errol HuellHonda CRX Turbo12.3613.3211513.2912315.726912.3610822.3652A10Errol HuellHonda CRX Turbo12.3613.3213.2915.7212.3622.36
131A14Westan FrickerFiat Coupe Turbo12.7812.781101A14Westan FrickerFiat Coupe Turbo12.7812.78
14A14Westan FrickerFiat Coupe Turbo12.8414.8411513.7411413.4011513.3011513.0511312.84109A14Westan FrickerFiat Coupe Turbo12.8414.8413.7413.4013.3013.0512.84
15A19Richard CooperRER Seat Ibiza12.9814.1711915.4510813.9710813.7111014.4010512.98119A19Richard CooperRER Seat Ibiza12.9814.1715.4513.9713.7114.4012.98
16A18Paul BargateVW Scirroco13.0613.5810437.128824.012313.06103A18Paul BargateVW Scirroco13.0613.5837.1224.0113.06
171A13Leighton WilsonFiat Coupe Turbo13.1413.1411713.798513.4811413.171161A13Leighton WilsonFiat Coupe Turbo13.1413.1413.7913.4813.17
18A13Leighton WilsonFiat Coupe Turbo13.1414.1911314.0711316.1410713.3211513.1411713.47115A13Leighton WilsonFiat Coupe Turbo13.1414.1914.0716.1413.3213.1413.47
19A12Nigel OgramFiat Coupe Turbo13.1614.0111014.4411114.1010813.16116A12Nigel OgramFiat Coupe Turbo13.1614.0114.4414.1013.16
20A16Mark CallisterEurospec Mitsubishi FTO13.2415.7910313.2410613.389627.084214.679415.8276A16Mark CallisterEurospec Mitsubishi FTO13.2415.7913.2413.3827.0814.6715.82
211A1David WilkinsonVauxhall Corsa13.3513.351051A1David WilkinsonVauxhall Corsa13.3513.35
22A15Richard BattyMitsubishi FTO13.3614.7410713.36129A15Richard BattyMitsubishi FTO13.3614.7413.36
231A16Mark CallisterEurospec Mitsubishi FTO13.4714.348914.1110413.4711014.161081A16Mark CallisterEurospec Mitsubishi FTO13.4714.3414.1113.4714.16
24A1David WilkinsonVauxhall Corsa13.5013.5110213.7710513.5910513.5010513.6210613.68102A1David WilkinsonVauxhall Corsa13.5013.5113.7713.5913.5013.6213.68
25A11Nick JonesTD Toyota Starlet13.5614.899115.929313.56105A11Nick JonesTD Toyota Starlet13.5614.8915.9213.56
26A3Gavin WorkerVauxhall Corsa13.6214.7210913.6211014.3011616.236813.71115A3Gavin WorkerVauxhall Corsa13.6214.7213.6214.3016.2313.71
271A19Richard CooperRER Seat Ibiza13.8114.359913.811091A19Richard CooperRER Seat Ibiza13.8114.3513.81
28
29Class B - Rear Wheel Drive
30B2John WebsterMG SVR9.809.8016010.021449.94144B2John WebsterMG SVR9.809.8010.0249.94
31B20Nathan FrekeToyota MR210.2510.6113612.888010.2513411.38125B20Nathan FrekeToyota MR210.2510.6112.8810.2511.38
32B37Nadine GearyDodge Viper10.7711.8013411.0713610.7713512.6913211.66135B37Nadine GearyDodge Viper10.7711.8011.0710.7712.6911.66
33B3Stuart BlackeryWestfield Cosworth10.9211.4013211.4312310.9212511.2612711.1612511.11127B3Stuart BlackeryWestfield Cosworth10.9211.4011.4310.9211.2611.1611.11
34B23Stephen RossFord Granada Twin Turbo11.2011.6313711.5411.8712111.3213111.2013811.34138B23Stephen RossFord Granada Twin Turbo11.2011.6311.5411.8711.3211.2011.34
351B23Stephen RossFord Granada Twin Turbo11.2411.2413811.321371B23Stephen RossFord Granada Twin Turbo11.2411.2411.32
36B36Carl BridsonDodge Viper11.4612.5411512.2812111.7112411.4612814.0113211.65132B36Carl BridsonDodge Viper11.4612.5412.2811.7111.4614.0111.65
37B1Duncan CowperDAX Rush Turbo11.4911.49126B1Duncan CowperDAX Rush Turbo11.4911.49
381B36Carl BridsonDodge Viper11.5111.6810911.5113411.581281B36Carl BridsonDodge Viper11.5111.6811.5111.58
391B16Larry VallotToyota MR211.6112.3511912.0812012.7611712.2311711.611211B16Larry VallotToyota MR211.6112.3512.0812.7612.2311.61
40B24Paul NorrisLotus Sunbeam Cosworth11.7314.8112712.1112112.6912311.9912812.3612511.73127B24Paul NorrisLotus Sunbeam Cosworth11.7314.8112.1112.6911.9912.3611.73
41B6Martyn WillisVX Polo 1.8T11.8917.734613.568214.786911.8911614.0875B6Martyn WillisVX Polo1.8T11.8917.7313.5614.7811.8914.08
42
Sheet1




Code: 
Option Explicit
Sub SplitData()
Dim c As Range, a As Long, b As Long, MyU As Long, LR As Long, LUC As Long
Dim Sp
Dim Hold As String
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If c.Value = "" Or Left(c.Value, 5) = "Class" Then
    'Do nothing
  Else
    Sp = Split(c.Value, ".")
    Hold = Left(Sp(0), Len(Sp(0)) - 2)
    MyU = 0
    For b = 1 To Len(Hold) Step 1
      Select Case Mid(Hold, b, 1)
        Case "A" To "Z"
          MyU = MyU + 1
          If MyU = 2 Then
            Cells(c.Row, 2) = Left(Hold, b - 1)
            Hold = Right(Hold, Len(Hold) - b + 1)
            Exit For
          End If
      End Select
    Next b
    MyU = 0
    For b = 1 To Len(Hold) Step 1
      Select Case Mid(Hold, b, 1)
        Case "A" To "Z"
          MyU = MyU + 1
          If MyU = 3 Then
            Cells(c.Row, 3) = Left(Hold, b - 1)
            Hold = Right(Hold, Len(Hold) - b + 1)
            Cells(c.Row, 4) = Hold
            Exit For
          End If
      End Select
    Next b
    b = UBound(Sp) - 1
    For a = LBound(Sp) To b
      Cells(c.Row, a + 5) = Right(Sp(a), 2) & "." & Left(Sp(a + 1), 2)
    Next a
  End If
Next c
LR = Cells(Rows.Count, 1).End(xlUp).Row
Application.Volatile
LUC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
Range(Cells(3, 5), Cells(LR, LUC)).NumberFormat = "0.00"
Application.ScreenUpdating = True
End Sub



I agree with jbeaucaire: If you can manage to get the providers of data to commas between the first few fields, this will be quite simple.


See my Private Message to you (top right hand corner of MrExcel, Welcome, leecavturbo., "Private Messages:".
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,221,579
Messages
6,160,616
Members
451,658
Latest member
NghiVmexgdhh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top