Sort by number with keeping same structure for data

M

muhleebbin

Active Member
Joined
Sep 30, 2017
Messages
252
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hi,

I have the following data that gets produced from our timesheet website however it aligns our employees in a funny format. Each employee has an employee number however it sorts all the 1's 10's and 100's first and then so on. Is there a way to resort this data using the employee number as the guide so that it is in numerical order?

Thank you for your assistance!

SUBMITTED TIME REPORT -- 08312020 - Mon - 09062020 - Sun (5).csv
ABCDEFGHIJKLMNOPQRS
1DateJobEmployeeTime InTime OutCost CodeDescriptionHoursEarn CodeAmountApproverEquipmentQtySubmitted ByShiftTradePhaseStatusRow Modified On
21 - A DoeSubtotal Hours : 40
38/31/2020Test1 - A Doe7:00 AM3:00 PM200 - Floor - Tile-8REG$0.00Test-0Test--140 - Hall 1Approved09/09/2020 5:51 pm UTC
49/1/2020Test1 - A Doe7:15 AM3:15 PM200 - Floor - Tile-8REG$0.00Test-0Test--134 - SaunaApproved09/09/2020 5:51 pm UTC
59/2/2020Test1 - A Doe7:00 AM3:00 PM200 - Floor - Tile-8REG$0.00Test-0Test--134 - SaunaApproved09/09/2020 5:51 pm UTC
69/3/2020Test1 - A Doe7:15 AM3:15 PM200 - Floor - Tile-8REG$0.00Test-0Test--121 - Mech. RoomApproved09/09/2020 5:51 pm UTC
79/4/2020Test1 - A Doe7:15 AM3:15 PM200 - Floor - Tile-8REG$0.00Test-0Test--121 - Mech. RoomApproved09/09/2020 5:51 pm UTC
8107 - B DoeSubtotal Hours : 32
98/31/2020Test107 - B Doe11:30 AM3:00 PM1200 - T&M - Demo Tile-3.5REG$0.00Test-0Test--1 - Existing PoolApproved09/09/2020 5:51 pm UTC
108/31/2020Test107 - B Doe6:30 AM11:00 AM1200 - T&M - Demo Tile-4.5REG$0.00Test-0Test--127 - Party Hall FloorApproved09/09/2020 5:51 pm UTC
119/1/2020Test107 - B Doe11:30 AM3:00 PM1200 - T&M - Demo Tile-3.5REG$0.00Test-0Test--1 - Existing PoolApproved09/09/2020 5:51 pm UTC
129/1/2020Test107 - B Doe6:30 AM11:00 AM1200 - T&M - Demo Tile-4.5REG$0.00Test-0Test--1 - Existing PoolApproved09/09/2020 5:51 pm UTC
139/2/2020Test107 - B Doe11:30 AM3:00 PM1200 - T&M - Demo Tile-3.5REG$0.00Test-0Test--109 - Catering Kitchen R106 - TileApproved09/09/2020 5:51 pm UTC
149/2/2020Test107 - B Doe6:30 AM11:00 AM1200 - T&M - Demo Tile-4.5REG$0.00Test-0Test--109 - Catering Kitchen R106 - TileApproved09/09/2020 5:51 pm UTC
159/3/2020Test107 - B Doe11:30 AM3:00 PM1000 - T&M Tile Prep-3.5REG$0.00Test-0Test--109 - Catering Kitchen R106 - TileApproved09/09/2020 5:51 pm UTC
169/3/2020Test107 - B Doe6:30 AM11:00 AM1200 - T&M - Demo Tile-4.5REG$0.00Test-0Test--109 - Catering Kitchen R106 - TileApproved09/09/2020 5:51 pm UTC
17112 - C DoeSubtotal Hours : 49.5
188/31/2020Test112 - C Doe7:00 AM11:00 AM1 - General Costs-4REG$0.00Test-0Test--1 - NO PHASEApproved09/03/2020 12:48 am UTC
198/31/2020Test112 - C Doe11:00 AM4:00 PM300 - Vanity - Fabrication-5REG$0.00Test-0Test--102 - Master BathroomApproved09/09/2020 5:51 pm UTC
208/31/2020Test112 - C Doe6:00 AM6:30 AM210 - Backsplash - Fabrication-0.5REG$0.00Test-0Test--154 - MudroomApproved09/09/2020 5:51 pm UTC
218/31/2020Test112 - C Doe6:30 AM7:00 AM210 - Backsplash - Fabrication-0.5REG$0.00Test-0Test--101 - Master 2 BathApproved09/09/2020 5:51 pm UTC
229/1/2020Test112 - C Doe6:00 AM1:00 PM300 - Vanity - Fabrication-7REG$0.00Test-0Test--102 - Master BathroomApproved09/09/2020 5:51 pm UTC
239/1/2020Test112 - C Doe1:00 PM4:00 PM310 - Countertop - Fabrication-3REG$0.00Test-0Test--131 - Laundry Room/Mud RoomApproved09/09/2020 5:51 pm UTC
249/2/2020Test112 - C Doe11:30 AM4:00 PM206 - Shower - Fabrication-4.5REG$0.00Test-0Test--102 - Master BathroomApproved09/09/2020 5:51 pm UTC
259/2/2020Test112 - C Doe6:00 AM11:30 AM300 - Vanity - Fabrication-5.5REG$0.00Test-0Test--102 - Master BathroomApproved09/09/2020 5:51 pm UTC
269/3/2020Test112 - C Doe6:00 AM7:00 AM206 - Shower - Fabrication-1REG$0.00Test-0Test--102 - Master BathroomApproved09/09/2020 5:51 pm UTC
279/3/2020Test112 - C Doe7:00 AM8:00 AM300 - Vanity - Fabrication-1REG$0.00Test-0Test--102 - Master BathroomApproved09/09/2020 5:51 pm UTC
289/3/2020Test112 - C Doe8:00 AM9:00 AM210 - Backsplash - Fabrication-1REG$0.00Test-0Test--102 - Master BathroomApproved09/09/2020 5:51 pm UTC
299/3/2020Test112 - C Doe9:00 AM4:00 PM700 - Fireplace - Fabrication-7REG$0.00Test-0Test--107 - Master BedroomApproved09/09/2020 5:51 pm UTC
309/4/2020Test112 - C Doe1:00 PM4:00 PM20 - Shop and Equipment Maintenance-3REG$0.00Test-0Test--125 - LMT ShopApproved09/09/2020 5:51 pm UTC
319/4/2020Test112 - C Doe6:00 AM8:00 AM700 - Fireplace - Fabrication-2REG$0.00Test-0Test--107 - Master BedroomApproved09/09/2020 5:51 pm UTC
329/4/2020Test112 - C Doe8:00 AM12:30 PM207 - Tub Wall- Fabrication-4.5REG$0.00Test-0Test--102 - Master BathroomApproved09/09/2020 5:51 pm UTC
33114 - E DoeSubtotal Hours : 39
348/31/2020Test114 - E Doe11:30 AM3:00 PM1201 - T&M - Install Tile & Coping-3.5REG$0.00Test-0Test--1 - Existing PoolApproved09/09/2020 5:51 pm UTC
358/31/2020Test114 - E Doe6:30 AM11:00 AM1201 - T&M - Install Tile & Coping-4.5REG$0.00Test-0Test--1 - Existing PoolApproved09/09/2020 5:51 pm UTC
369/1/2020Test114 - E Doe11:30 AM3:00 PM1201 - T&M - Install Tile & Coping-3.5REG$0.00Test-0Test--1 - Existing PoolApproved09/09/2020 5:51 pm UTC
379/1/2020Test114 - E Doe6:30 AM11:00 AM1201 - T&M - Install Tile & Coping-4.5REG$0.00Test-0Test--1 - Existing PoolApproved09/09/2020 5:51 pm UTC
389/2/2020Test114 - E Doe11:30 AM3:00 PM1201 - T&M - Install Tile & Coping-3.5REG$0.00Test-0Test--1 - Existing PoolApproved09/03/2020 10:08 am UTC
399/2/2020Test114 - E Doe6:30 AM11:00 AM1201 - T&M - Install Tile & Coping-4.5REG$0.00Test-0Test--1 - Existing PoolApproved09/03/2020 10:08 am UTC
409/3/2020Test114 - E Doe11:30 AM3:00 PM1201 - T&M - Install Tile & Coping-3.5REG$0.00Test-0Test--1 - Existing PoolApproved09/09/2020 5:51 pm UTC
419/3/2020Test114 - E Doe6:30 AM11:00 AM1201 - T&M - Install Tile & Coping-4.5REG$0.00Test-0Test--1 - Existing PoolApproved09/09/2020 5:51 pm UTC
429/4/2020Test114 - E Doe6:30 AM1:30 PM1201 - T&M - Install Tile & Coping-7REG$0.00Test-0Test--1 - Existing PoolApproved09/09/2020 5:51 pm UTC
4314 - F DoeSubtotal Hours : 39
448/31/2020Test14 - F Doe2:00 PM3:00 PM20 - Shop and Equipment Maintenance-1REG$0.00Test-0Test--125 - Lander MaintenanceApproved09/09/2020 5:51 pm UTC
458/31/2020Test14 - F Doe7:00 AM9:00 AM20 - Shop and Equipment Maintenance-2REG$0.00Test-0Test--125 - Lander MaintenanceApproved09/09/2020 5:51 pm UTC
468/31/2020Test14 - F Doe9:00 AM1:00 PM601 - Tub Surround - Slab-4REG$0.00Test-0Test--103 - Bathroom 2Approved09/09/2020 5:51 pm UTC
478/31/2020Test14 - F Doe1:00 PM2:00 PM10 - Pickups and Delivery-1REG$0.00Test-0Test--155 - ServiceApproved09/09/2020 5:51 pm UTC
489/1/2020Test14 - F Doe10:30 AM12:30 PM311 - Countertop - Slab-2REG$0.00Test-0Test--109 - Kitchen 1Approved09/09/2020 5:51 pm UTC
499/1/2020Test14 - F Doe12:30 PM2:30 PM301 - Vanity - Slab-2REG$0.00Test-0Test--103 - Bathroom 2Approved09/09/2020 5:51 pm UTC
509/1/2020Test14 - F Doe6:30 AM10:30 AM601 - Tub Surround - Slab-4REG$0.00Test-0Test--101 - Bathroom 1Approved09/09/2020 5:51 pm UTC
519/2/2020Test14 - F Doe7:00 AM11:00 AM20 - Shop and Equipment Maintenance-4REG$0.00Test-0Test--127 - Forest MaintenanceApproved09/09/2020 5:51 pm UTC
529/2/2020Test14 - F Doe11:00 AM3:00 PM311 - Countertop - Slab-4REG$0.00Test-0Test--109 - Kitchen 1Approved09/09/2020 5:51 pm UTC
539/3/2020Test14 - F Doe1:00 PM3:00 PM321 - Island - Slab-2REG$0.00Test-0Test--109 - Kitchen 1Approved09/09/2020 5:51 pm UTC
549/3/2020Test14 - F Doe7:00 AM1:00 PM311 - Countertop - Slab-6REG$0.00Test-0Test--109 - Kitchen 1Approved09/09/2020 5:51 pm UTC
559/4/2020Test14 - F Doe12:00 PM2:00 PM321 - Island - Slab-2REG$0.00Test-0Test--109 - Kitchen 1Approved09/09/2020 5:51 pm UTC
569/4/2020Test14 - F Doe7:00 AM12:00 PM311 - Countertop - Slab-5REG$0.00Test-0Test--109 - Kitchen 1Approved09/09/2020 5:51 pm UTC
57143 - G DoeSubtotal Hours : 38.5
588/31/2020Test143 - G Doe1:00 PM4:30 PM402 - Shower - Slab-3.5REG$0.00Test-0Test--101 - Master 2 BathApproved09/09/2020 5:51 pm UTC
598/31/2020Test143 - G Doe7:30 AM12:00 PM301 - Vanity - Slab-4.5REG$0.00Test-0Test--101 - Master 2 BathApproved09/09/2020 5:51 pm UTC
609/1/2020Test143 - G Doe11:30 AM2:30 PM211 - Backsplash - Slab-3REG$0.00Test-0Test--105 - Bunk BathApproved09/09/2020 5:51 pm UTC
619/1/2020Test143 - G Doe2:30 PM3:30 PM402 - Shower - Slab-1REG$0.00Test-0Test--101 - Master 2 BathApproved09/09/2020 5:51 pm UTC
629/1/2020Test143 - G Doe7:30 AM10:30 AM402 - Shower - Slab-3REG$0.00Test-0Test--101 - Master 2 BathApproved09/09/2020 5:51 pm UTC
639/2/2020Test143 - G Doe2:00 PM4:00 PM402 - Shower - Slab-2REG$0.00Test-0Test--104 - Master 4 BathApproved09/09/2020 5:51 pm UTC
649/2/2020Test143 - G Doe7:30 AM9:00 AM301 - Vanity - Slab-1.5REG$0.00Test-0Test--101 - Master 2 BathApproved09/09/2020 5:51 pm UTC
659/2/2020Test143 - G Doe9:00 AM12:00 PM402 - Shower - Slab-3REG$0.00Test-0Test--101 - Master 2 BathApproved09/09/2020 5:51 pm UTC
669/3/2020Test143 - G Doe3:30 PM4:15 PM402 - Shower - Slab-0.75REG$0.00Test-0Test--102 - Master BathroomApproved09/09/2020 5:51 pm UTC
679/3/2020Test143 - G Doe7:00 AM9:00 AM402 - Shower - Slab-2REG$0.00Test-0Test--101 - Master 2 BathApproved09/09/2020 5:51 pm UTC
689/3/2020Test143 - G Doe9:00 AM3:30 PM301 - Vanity - Slab-6.5REG$0.00Test-0Test--102 - Master BathroomApproved09/09/2020 5:51 pm UTC
699/4/2020Test143 - G Doe12:15 PM4:00 PM601 - Tub Surround - Slab-3.75REG$0.00Test-0Test--101 - Camp BathApproved09/09/2020 5:51 pm UTC
709/4/2020Test143 - G Doe7:15 AM11:15 AM402 - Shower - Slab-4REG$0.00Test-0Test--102 - Master BathroomApproved09/09/2020 5:51 pm UTC
SUBMITTED TIME REPORT -- 083120
 
Sort by date Sort by votes
you can use Power Query (for my table above) and it should be sorted properly all the time, just Refresh after update
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Condition = Table.AddColumn(Source, "Custom", each if [Employee] = null then [Date] else [Employee]),
    TBD = Table.AddColumn(Condition, "TBD", each Text.BeforeDelimiter([Employee], " -"), type text),
    Replace = Table.ReplaceValue(TBD,"",null,Replacer.ReplaceValue,{"TBD"}),
    FillUp = Table.FillUp(Replace,{"TBD"}),
    TextPad = Table.AddColumn(FillUp, "Custom.1", each Text.PadStart([TBD],3,"0")),
    Index = Table.AddIndexColumn(TextPad, "Index", 1, 1),
    Sort = Table.Sort(Index,{{"Custom.1", Order.Ascending}, {"Index", Order.Ascending}}),
    RC = Table.RemoveColumns(Sort,{"Custom", "TBD", "Custom.1", "Index"}),
    Type = Table.TransformColumnTypes(RC,{{"Time In", type time}, {"Time Out", type time}})
in
    Type

also with PQ you can transform source table to proper table structure and type of data
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Fluff, thank you for putting that into a macro. This took the employee over an hour to configure manually and now can be done in seconds!

Thank you all for your assistance!
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
Fluff,

This is probably a secondary ask but may be possible here but with the macro is it possible to sort by further columns after the initial sort? I'm looking to sort by the date (which it seems to do naturally) but then by Column D in chronological order. If you look at employee 107 - B Doe and 112 - C Doe you could see that for the date 8/31/20 line 10 should be before 9 and the order for 112 should be 20, 21, 18 and 19 respectively etc.

Obviously still trying to keep the same structure for the data so that the employee and their respective times are still together.
 
Upvote 0
How about
VBA Code: 
Sub muhleebbin()
   Range("T1").Value = "sort"
   With Range("T2:T" & Range("A" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace(Replace("if(@<>"""",left(@,find("" "",@))+.1,left(#,find("" "",#)))", "@", .Offset(, -17).Address), "#", .Offset(1, -17).Address))
   End With
   Range("a1").CurrentRegion.Sort Range("T1"), xlAscending, Range("A1"), , xlAscending, Range("D1"), xlAscending, xlYes
End Sub
 
Upvote 0
Solution
ahhh I love ya!

I found my co worker still cutting and pasting the data to make it look like this! o_O
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
311
awoohaw
awoohaw
K
  • Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
156
XLfriend777
XLfriend777
L
  • Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
237
Lukma
L
R
  • Solved
Countif formula to not count same cell twice
2
Replies
10
Views
1K
Eric W
Eric W
L
  • Question
Formula Help with Count of Week Numbers for every Each Month
2
Replies
19
Views
973
Armstrong_N14
A

Forum statistics

Threads
1,215,045
Messages
6,122,836
Members
449,096
Latest member
Erald

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