sorting by multiple columns issue with day of the week

R

RhodEvans

Board Regular
Joined
Oct 31, 2012
Messages
88
Afternoon,

I have a spreadsheet that I am trying to write a macro to sort First by Column B (Date), then by Column A (Day) then by column C (Group).
I realise that sorting by date then day may seem redundant, but the date may not always be filled in.
My issue is that I can only seem to get the weekday sort order on the 1st key (column A) and not on Column B (Day) like I need. Below is the code I have got so far from recording the action. Does anyone know how this needs to be amended to do what I need. If so I would be very grateful.

Code: 
Cells.Select
    Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("A2") _
        , Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=3, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Apologies for the delay in returning. I had to anonomise the data

DAY
DATEGROUP ID Name AMOUNT Cash/Warrant TAXI, RAIL or BUSWarrant detailsAttendednext session date
MONDAY28/12/13MPSRG BL BIE|02-13 PB
nana NO travel required
MONDAY28/12/13MPSRG BL BIE|02-13 CR 12warranttrainTrain warrant Peterborough to Bicester
MONDAY28/12/13MPSRG BL BIE|02-13 ML FB nana NO travel required
MONDAY28/12/13MPSRG BL BIE|02-13 DFC nana NO travel required
MONDAY28/12/13MPSRG BL BIE|02-13 DFC 32warrantBusTaxi Stevenage to CMK - X5 CMK to Bicester- Taxi Bicester
MONDAY MPSRG BL BIE|02-13 dj 5.83cash low income
MONDAY MPSRG BL BIE|02-13 kf 8warranttrainX5 or S5 Bus Warrant Oxford- Abbingdon
MONDAY MPSRG BL BIE|02-13 shjs nana NO travel required
MONDAY MPSRG BL BIE|02-13 auidf 12warranttrainTrain warrant Aylesbury to Rumford
MONDAY MPSRG BL BIE|02-13 hf nana NO travel required
MONDAY MPSRG BL BIE|02-13 w nana NO travel required
MONDAY MPSRG BL BIE|02-13 sj 32warrantBusTaxi Stevenage to CMK - X5 CMK to Bicester- Taxi Bicester
MONDAY MPSRG BL BIE|02-13 d 5.83cash low income
MONDAY MPSRG BL BIE|02-13 fd 8warranttrainX5 or S5 Bus Warrant Oxford- London
MONDAY MPSRG BL BIE|02-13 fs 1.24cash low income
MONDAY
MPSRG BL BIE|02-13 ws 3.81cash low income
MONDAY MPSRG BL BIE|02-13 s 6.6cash Newport Pagnell to Northampton
THURSDAY CDVP BICE29 DFC nananana 21/11/2013
THURSDAY CDVP BICE29 wr nananana 21/11/2013
THURSDAY CDVP BICE29 fx nananana 21/11/2013
THURSDAY CDVP BICE29 xc nanananastarts 28.11.1321/11/2013
THURSDAY CDVP BICE29 sd nananana 21/11/2013
THURSDAY CDVP BICE29 as nananana 21/11/2013
THURSDAY CDVP BICE29 fd nananana 21/11/2013
THURSDAY CDVP BICE29 er nanananastarts 28.11.1321/11/2013
THURSDAY CDVP BICE29 DFC 7.60warranttrainCardiff To Bicester Rtn 21/11/2013
THURSDAY CDVP BICE29 hg 17.00warranttrainWendover to Swindon (Via Aylesbury and Princes Risborough) Return 21/11/2013
THURSDAY CDVP BICE29 yu nananana 21/11/2013
THURSDAY TSPBICR1 O SCM hg Taxis
share		Milton Keynes to Coventry Return 12/11/2013
THURSDAY TSPBICR1 O SCM nb Shared
Taxis		Taxis Aylesbury to Stafford Return 12/11/2013
THURSDAY TSPBICR1 O SCM cv Shared
Taxis		Milton Keynes to Stone Henge Return. 12/11/2013
THURSDAY TSPBICR1 O SCM DFC TrainHigh Wycombe to Windsor Return 12/11/2013
THURSDAY TSPBICR1 O SCM er Bus + Shared TaxisBus Amersham to Chesham return. Taxis Chesham to Leighton Return 12/11/2013
THURSDAY TSPBICR1 O SCM DFC Bus + TaxiBus from Amersham to Leighton return. Then Taxis from Leighton to Bicester return. 12/11/2013
THURSDAY TSPBICR1 O SCM vc Shared TaxisCentral Milton Keynes to Cambridge return 12/11/2013
THURSDAY TSPBICR1 O SCM fd TaxiAylesbury to Dover Return 12/11/2013
THURSDAY MPSRG BL BIE|02-13 sd nana NO travel required
THURSDAY MPSRG BL BIE|02-13 gf 12warranttrainTrain warrant Shringingham to Bicester
THURSDAY MPSRG BL BIE|02-13 tr nana NO travel required
THURSDAY MPSRG BL BIE|02-13 hg nana NO travel required
THURSDAY MPSRG BL BIE|02-13 bvb 32warrantBusTaxi Great Holm to CMK - X5 CMK to Bicester-
THURSDAY MPSRG BL BIE|02-13 sd 5.83cash low income
THURSDAY MPSRG BL BIE|02-13 bvb 8warranttrainX5 or S5 Bus Warrant Stoke- bicester
THURSDAY MPSRG BL BIE|02-13 DFC 1.24cash low income
THURSDAY MPSRG BL BIE|02-13 xc 3.81cash low income
THURSDAY MPSRG BL BIE|02-13 as 6.6cash Newport Pagnell to Heathrow
TUESDAY TSPBICR1 O SCM fd Shared
Taxis		Taxis Aylesbury to Glasgow Return EMR-TBA
TUESDAY TSPBICR1 O SCM gf TrainHigh Wycombe to Birmingham Return EMR-TBA
TUESDAY TSPBICR1 O SCM hg Bus + TaxiBus from Canturbry to Chesham return. EMR-TBA
TUESDAY TSPBICR1 O SCM j Shared TaxisCentral Milton Keynes to Long Hmabridge return EMR-TBA
TUESDAY TSPBICR1 O SCM hb TaxiAylesbury to Widdle Return EMR-TBA
WEDNESDAY CDVP BICE30 nb na TaxiAylesbury to Bicester, Rtn 20/11/2013
WEDNESDAY CDVP BICE30 cv nananaNO travel required starts 6.11.1320/11/2013
WEDNESDAY CDVP BICE30 xc nananaNO travel required 20/11/2013
WEDNESDAY CDVP BICE30 sd nananaNO travel required 20/11/2013
WEDNESDAY CDVP BICE30 cv 16.20naTrain & BusBus to HW train station, train HW to Towcester & Rtn 20/11/2013
WEDNESDAY CDVP BICE30 DFC 12.00naTrainWarrick to Bicester Rtn 20/11/2013
WEDNESDAY CDVP BICE30 cv nanaTaxiMoamar to Bicester, Rtn 20/11/2013
WEDNESDAY CDVP BICE30 bvb nana NO travel required 20/11/2013
WEDNESDAY CDVP BICE30 fg nana NO travel required 20/11/2013
WEDNESDAY CDVP BICE30 cv 20.20warrantBus & TrainStains to High Wycombe Centre Return. Train High Wycombe to Bicester Return. 20/11/2013
WEDNESDAY CDVP BICE30 DFC nananaNO travel required 20/11/2013
WEDNESDAY CDVP BICE30 er nananaNO travel required 20/11/2013

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

As you can see at present there are no dates in the date column however when there are it will cause issues. As it should read Monday-Friday then Monday to Friday the next week (where dates are used) if there is no date then I am looking for it to be included in the first Monday-Friday run.
Hope that makes sense.

Thanks in advance
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Custom Sorting in VBA
Replies
3
Views
92
Micron
M
H
  • Solved
Column sort
Replies
9
Views
264
Fluff
Fluff
T
  • Solved
Error when sorting table - with exact same code that is working elsewhere
Replies
2
Views
175
TheRedCardinal
T
A
  • Solved
Dual Criteria Sort failing
Replies
5
Views
398
Fluff
Fluff
R
  • Question
Macro not working after adding two new items
Replies
5
Views
244
regan_2000
R

Forum statistics

Threads
1,217,132
Messages
6,134,810
Members
449,890
Latest member
xpat

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