Advanced sorting in Excel (2010)

SARSteam

SARSteam

New Member
Joined
Jul 16, 2013
Messages
2
Good day
The challenge I have before:

The Data I receive from our Mainframe as per the Table hereunder (Total of ± 19000 records per week). First I removed duplicates (which I did not do in the table hereunder ) then I need to sort Column E (Time) to reflect 1) Arrivals - Minimum amount of time and 2) Departures - Maximum amount of time. (Arrivals and Departures are indicated in Column D.)</SPAN>

These 19 000 entries are in groups of 4. So all records need to be seen as groups of 4 then sorted as such. Other than the records mentioned above must be deleted.

ie: Rows 2 to 5 is one record: Only row 3 or 4 (Max time) must stay and the other 3 deleted as per the Key row which is D - Departures.
ie: Rows 6 to 9 is the second record: In this case I need the Earliest time which is row 9 (Min Time). The other 3 must be deleted.

Has anyone -perhaps had a similar challenge?

[TABLE="width: 447"]
<COLGROUP><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 48pt" span=3 width=64><TBODY>[TR]
[TD="class: xl72, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl72, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl72, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl72, width: 64, bgcolor: transparent"]C[/TD]
[TD="class: xl72, width: 64, bgcolor: transparent"]D[/TD]
[TD="class: xl72, width: 111, bgcolor: transparent"]E[/TD]
[TD="class: xl72, width: 64, bgcolor: transparent"]F[/TD]
[TD="class: xl72, width: 64, bgcolor: transparent"]G[/TD]
[TD="class: xl72, width: 64, bgcolor: transparent"]H[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]1[/TD]
[TD="class: xl70, bgcolor: transparent"] ctc[/TD]
[TD="class: xl70, bgcolor: transparent"]trainno[/TD]
[TD="class: xl70, bgcolor: transparent"]position[/TD]
[TD="class: xl70, bgcolor: transparent"]arv / dep[/TD]
[TD="class: xl71, bgcolor: transparent"]time[/TD]
[TD="class: xl70, bgcolor: transparent"]platno[/TD]
[TD="class: xl70, bgcolor: transparent"]shortno[/TD]
[TD="class: xl70, bgcolor: transparent"]Loop[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]2[/TD]
[TD="class: xl68, bgcolor: transparent"]sax[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl68, bgcolor: transparent"]LZS[/TD]
[TD="class: xl68, bgcolor: transparent"]D[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]2013/06/24 00:42[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl68, bgcolor: transparent"]Lus 1[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]3[/TD]
[TD="class: xl68, bgcolor: transparent"]sax[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl68, bgcolor: transparent"]LZS[/TD]
[TD="class: xl68, bgcolor: transparent"]D[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]2013/06/24 00:39[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl68, bgcolor: transparent"]Lus 1[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]4[/TD]
[TD="class: xl68, bgcolor: transparent"]sax[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl68, bgcolor: transparent"]LZS[/TD]
[TD="class: xl68, bgcolor: transparent"]D[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]2013/06/24 00:39[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl68, bgcolor: transparent"]Lus 1[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]5[/TD]
[TD="class: xl68, bgcolor: transparent"]sax[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl68, bgcolor: transparent"]LZS[/TD]
[TD="class: xl68, bgcolor: transparent"]D[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]2013/06/24 00:38[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl68, bgcolor: transparent"]Lus 1[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]6[/TD]
[TD="class: xl66, bgcolor: transparent"]sax[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl66, bgcolor: transparent"]LZM[/TD]
[TD="class: xl66, bgcolor: transparent"]A[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2013/06/24 01:25[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]101[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl66, bgcolor: transparent"]Lus 2[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]7[/TD]
[TD="class: xl66, bgcolor: transparent"]sax[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl66, bgcolor: transparent"]LZM[/TD]
[TD="class: xl66, bgcolor: transparent"]A[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2013/06/24 01:22[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]101[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl66, bgcolor: transparent"]Lus 2[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]8[/TD]
[TD="class: xl66, bgcolor: transparent"]sax[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl66, bgcolor: transparent"]LZM[/TD]
[TD="class: xl66, bgcolor: transparent"]A[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2013/06/24 01:22[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]101[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl66, bgcolor: transparent"]Lus 2[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]9[/TD]
[TD="class: xl66, bgcolor: transparent"]sax[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl66, bgcolor: transparent"]LZM[/TD]
[TD="class: xl66, bgcolor: transparent"]A[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2013/06/24 01:20[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]101[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl66, bgcolor: transparent"]Lus 2[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]10[/TD]
[TD="class: xl68, bgcolor: transparent"]sax[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl68, bgcolor: transparent"]LZM[/TD]
[TD="class: xl68, bgcolor: transparent"]D[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]2013/06/24 01:27[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl68, bgcolor: transparent"]Lus 2[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]11[/TD]
[TD="class: xl68, bgcolor: transparent"]sax[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl68, bgcolor: transparent"]LZM[/TD]
[TD="class: xl68, bgcolor: transparent"]D[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]2013/06/24 01:22[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl68, bgcolor: transparent"]Lus 2[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]12[/TD]
[TD="class: xl68, bgcolor: transparent"]sax[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl68, bgcolor: transparent"]LZM[/TD]
[TD="class: xl68, bgcolor: transparent"]D[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]2013/06/24 01:27[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl68, bgcolor: transparent"]Lus 2[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]13[/TD]
[TD="class: xl68, bgcolor: transparent"]sax[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl68, bgcolor: transparent"]LZM[/TD]
[TD="class: xl68, bgcolor: transparent"]D[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]2013/06/24 01:35[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl68, bgcolor: transparent"]Lus 2[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]14[/TD]
[TD="class: xl66, bgcolor: transparent"]sax[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl66, bgcolor: transparent"]LWC[/TD]
[TD="class: xl66, bgcolor: transparent"]A[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2013/06/24 02:08[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]101[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl66, bgcolor: transparent"]Lus 3[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]15[/TD]
[TD="class: xl66, bgcolor: transparent"]sax[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl66, bgcolor: transparent"]LWC[/TD]
[TD="class: xl66, bgcolor: transparent"]A[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2013/06/24 02:09[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]101[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl66, bgcolor: transparent"]Lus 3[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]16[/TD]
[TD="class: xl66, bgcolor: transparent"]sax[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl66, bgcolor: transparent"]LWC[/TD]
[TD="class: xl66, bgcolor: transparent"]A[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2013/06/24 02:12[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]101[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl66, bgcolor: transparent"]Lus 3[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]17[/TD]
[TD="class: xl66, bgcolor: transparent"]sax[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91230613[/TD]
[TD="class: xl66, bgcolor: transparent"]LWC[/TD]
[TD="class: xl66, bgcolor: transparent"]A[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2013/06/24 02:09[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]101[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]91[/TD]
[TD="class: xl66, bgcolor: transparent"]Lus 3[/TD]
[/TR]
</TBODY>[/TABLE]
 

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.
You must log in or register to reply here.

Similar threads

E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
126
ellison
E
M
  • Question Question
Macro or Power Automate to Identify and Move "Washes" from the "Master" Sheet to the "Washes" Sheet
Replies
0
Views
172
Madam_Xavier
M
E
  • Solved
Find a value matching a range upon several criteria
Replies
2
Views
156
ED38
E
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
310
JMWh1t3
J
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
588
awoohaw
awoohaw

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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