Trying to sort by date, but group matching field

R

rondeaul

New Member
Joined
Sep 14, 2017
Messages
10
I've been all over the different questions and answers and can't seem to find one that fits my situation.
I have a large list of data (sales orders) where we might get multiple sales orders for the same client. I'm trying to sort the overall list by date but then group all orders for the same client together so the newer orders are brought forward with the oldest sales order for that client. The key is we are working on clients with the oldest sales order first, so I don't simply want to sort by client and then date. I'm not sure how to get where I need to go. Any suggestions?


[TABLE="width: 797"]
<colgroup><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 4"][TABLE="width: 813"]
<colgroup><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 4"]Current output[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Desired Output[/TD]
[/TR]
[TR]
[TD]Client Name[/TD]
[TD]Status[/TD]
[TD]Order Date[/TD]
[TD]Client #[/TD]
[TD][/TD]
[TD][/TD]
[TD]Client Name[/TD]
[TD]Status[/TD]
[TD]Order Date[/TD]
[TD]Client #[/TD]
[/TR]
[TR]
[TD]client 12[/TD]
[TD]Active[/TD]
[TD]5/17/2018[/TD]
[TD]1620858[/TD]
[TD][/TD]
[TD][/TD]
[TD]client 12[/TD]
[TD]Active[/TD]
[TD]5/17/2018[/TD]
[TD]1620858[/TD]
[/TR]
[TR]
[TD]Client 1[/TD]
[TD]Active[/TD]
[TD]6/1/2018[/TD]
[TD]1628914[/TD]
[TD][/TD]
[TD][/TD]
[TD]client 12[/TD]
[TD]Active[/TD]
[TD]6/23/2018[/TD]
[TD]1620858[/TD]
[/TR]
[TR]
[TD]client 10[/TD]
[TD]Hold[/TD]
[TD]6/5/2018[/TD]
[TD]1630746[/TD]
[TD][/TD]
[TD][/TD]
[TD]Client 1[/TD]
[TD]Active[/TD]
[TD]6/1/2018[/TD]
[TD]1628914[/TD]
[/TR]
[TR]
[TD]client 4[/TD]
[TD]Hold[/TD]
[TD]6/5/2018[/TD]
[TD]1630546[/TD]
[TD][/TD]
[TD][/TD]
[TD]Client 1[/TD]
[TD]Active[/TD]
[TD]6/23/2018[/TD]
[TD]1628914[/TD]
[/TR]
[TR]
[TD]client 9[/TD]
[TD]Hold[/TD]
[TD]6/5/2018[/TD]
[TD]1630410[/TD]
[TD][/TD]
[TD][/TD]
[TD]client 10[/TD]
[TD]Hold[/TD]
[TD]6/5/2018[/TD]
[TD]1630746[/TD]
[/TR]
[TR]
[TD]client 7[/TD]
[TD]Active[/TD]
[TD]6/7/2018[/TD]
[TD]1479678[/TD]
[TD][/TD]
[TD][/TD]
[TD]client 10[/TD]
[TD]Hold[/TD]
[TD]6/23/2018[/TD]
[TD]1630746[/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]Hold[/TD]
[TD]6/11/2018[/TD]
[TD]1634284[/TD]
[TD][/TD]
[TD][/TD]
[TD]client 4[/TD]
[TD]Hold[/TD]
[TD]6/5/2018[/TD]
[TD]1630546[/TD]
[/TR]
[TR]
[TD]Client 1[/TD]
[TD]Active[/TD]
[TD]6/23/2018[/TD]
[TD]1628914[/TD]
[TD][/TD]
[TD][/TD]
[TD]client 4[/TD]
[TD]Hold[/TD]
[TD]6/23/2018[/TD]
[TD]1630546[/TD]
[/TR]
[TR]
[TD]client 10[/TD]
[TD]Hold[/TD]
[TD]6/23/2018[/TD]
[TD]1630746[/TD]
[TD][/TD]
[TD][/TD]
[TD]client 9[/TD]
[TD]Hold[/TD]
[TD]6/5/2018[/TD]
[TD]1630410[/TD]
[/TR]
[TR]
[TD]client 11[/TD]
[TD]Rolled[/TD]
[TD]6/23/2018[/TD]
[TD]0128596[/TD]
[TD][/TD]
[TD][/TD]
[TD]client 7[/TD]
[TD]Active[/TD]
[TD]6/7/2018[/TD]
[TD]1479678[/TD]
[/TR]
[TR]
[TD]client 12[/TD]
[TD]Active[/TD]
[TD]6/23/2018[/TD]
[TD]1620858[/TD]
[TD][/TD]
[TD][/TD]
[TD]Client 2[/TD]
[TD]Hold[/TD]
[TD]6/11/2018[/TD]
[TD]1634284[/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]Hold[/TD]
[TD]6/23/2018[/TD]
[TD]1634284[/TD]
[TD][/TD]
[TD][/TD]
[TD]Client 2[/TD]
[TD]Hold[/TD]
[TD]6/23/2018[/TD]
[TD]1634284[/TD]
[/TR]
[TR]
[TD]client 4[/TD]
[TD]Hold[/TD]
[TD]6/23/2018[/TD]
[TD]1630546[/TD]
[TD][/TD]
[TD][/TD]
[TD]client 11[/TD]
[TD]Rolled[/TD]
[TD]6/23/2018[/TD]
[TD]0128596[/TD]
[/TR]
[TR]
[TD]client 8[/TD]
[TD]Active[/TD]
[TD]6/23/2018[/TD]
[TD]0632193[/TD]
[TD][/TD]
[TD][/TD]
[TD]client 8[/TD]
[TD]Active[/TD]
[TD]6/23/2018[/TD]
[TD]0632193[/TD]
[/TR]
[TR]
[TD]client 3[/TD]
[TD]Active[/TD]
[TD]8/2/2018[/TD]
[TD]1639915[/TD]
[TD][/TD]
[TD][/TD]
[TD]client 3[/TD]
[TD]Active[/TD]
[TD]8/2/2018[/TD]
[TD]1639915[/TD]
[/TR]
[TR]
[TD]client 5[/TD]
[TD]Active[/TD]
[TD]8/8/2018[/TD]
[TD]1623580[/TD]
[TD][/TD]
[TD][/TD]
[TD]client 3[/TD]
[TD]Active[/TD]
[TD]9/5/2018[/TD]
[TD]1639915[/TD]
[/TR]
[TR]
[TD]client 3[/TD]
[TD]Active[/TD]
[TD]9/5/2018[/TD]
[TD]1639915[/TD]
[TD][/TD]
[TD][/TD]
[TD]client 5[/TD]
[TD]Active[/TD]
[TD]8/8/2018[/TD]
[TD]1623580[/TD]
[/TR]
[TR]
[TD]client 6[/TD]
[TD]Active[/TD]
[TD]9/7/2018[/TD]
[TD]1589648[/TD]
[TD][/TD]
[TD][/TD]
[TD]client 6[/TD]
[TD]Active[/TD]
[TD]9/7/2018[/TD]
[TD]1589648[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Pivot Table Reporting:
Place the the fields in the ROWS area.
Also add the Oder date to the Values Area, Left click on the Order Date in Values area and select the Value Field Settings. Change to MIN.
From the Pivot Table ribbon/Design Ribbon, set the Report Layout to Tabular. Set Repeat All Labels
Under Grand Totals, select OFF for Row and Columns
Under Subtotals, Do not Show Subtotals.

On the pivot Table, Right Click a Client Name. Choose SORT... More Sort Options. Set for ASCENDING by Min of Order Date.
 
Upvote 0
Thank you SpillerBD, but I have too much data to be able to use a pivot table. I only included a small sample of the data but the report can run anywhere from 50 to 1,000+ rows and typically has about 70 columns. That is the overall data I am trying to sort and group.
 
Upvote 0
Too big for a Pivot Table? Only a thousand rows! Its the columns that get ugly and some of those other little quirks....

For the Range/Table you will need a helper column and array formula to group each Client Name by the MIN(Order Date), then you'd be able to sort by that date, the Client Name and the Order Date.

Where Column "A" refers to the column of Client Names and column "C" refers to the Order Date
Code: 
=MIN(IF($A$2:$A$19=A2,$C$2:$C$19))
Use CTRL+SHIFT+ENTER to enter formula as Array Formula.
 
Upvote 0
Not sure I fully understand the requirement, but adding this helper column and then sorting on that new column produces the expected results for the sample data.
(Note that my dates are in d/m/y format)

Excel Workbook
ABCDE
1Client NameStatusOrder DateClient #
2client 12Active17/05/201816208581
3Client 1Active6/01/201816289142
4client 10Hold6/05/201816307463
5client 4Hold6/05/201816305464
6client 9Hold6/05/201816304105
7client 7Active6/07/201814796786
8Client 2Hold6/11/201816342847
9Client 1Active23/06/201816289142
10client 10Hold23/06/201816307463
11client 11Rolled23/06/201812859610
12client 12Active23/06/201816208581
13Client 2Hold23/06/201816342847
14client 4Hold23/06/201816305464
15client 8Active23/06/201863219314
16client 3Active8/02/2018163991515
17client 5Active8/08/2018162358016
18client 3Active9/05/2018163991515
19client 6Active9/07/2018158964818
Sort




After sorting:

Excel Workbook
ABCDE
1Client NameStatusOrder DateClient #
2client 12Active17/05/201816208581
3client 12Active23/06/201816208581
4Client 1Active6/01/201816289143
5Client 1Active23/06/201816289143
6client 10Hold6/05/201816307465
7client 10Hold23/06/201816307465
8client 4Hold6/05/201816305467
9client 4Hold23/06/201816305467
10client 9Hold6/05/201816304109
11client 7Active6/07/2018147967810
12Client 2Hold6/11/2018163428411
13Client 2Hold23/06/2018163428411
14client 11Rolled23/06/201812859613
15client 8Active23/06/201863219314
16client 3Active8/02/2018163991515
17client 3Active9/05/2018163991515
18client 5Active8/08/2018162358017
19client 6Active9/07/2018158964818
Sort
 
Last edited:
Upvote 0
Thank you SpillerBD. This worked as well, but in the end I used the Match function below. I was able to build that into a macro easier.
 
Upvote 0
rondeaul said:
II'm trying to sort the overall list by date but then group all orders for the same client together so the newer orders are brought forward with the oldest sales order for that client. The key is we are working on clients with the oldest sales order first, so I don't simply want to sort by client and then date.
Click to expand...
It's not obvious because of the example used, but Peter's solution does not meet this criteria.
Resort by name and then try to resort for the desired results....
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Number of Weeks Between First and Last of a List of Dates
Replies
7
Views
195
hagia_sofia
hagia_sofia
R
  • Question Question
Excel date format
Replies
7
Views
502
Cubist
Cubist
HansM
  • Question Question
Looking for a specific number in a cell that can have multiple numbers separated by a comma (Using Mac Excel)
Replies
5
Views
296
Jeffrey Mahoney
Jeffrey Mahoney
M
  • Solved
Optimizing my rows to send e-mail to different address
Replies
2
Views
169
MasterBash
M
S
  • Question Question
Pulling Correct Number of Dates Based on Column Value
Replies
1
Views
41
dreid1011
dreid1011

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

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