Trying to sort by date, but group matching field

[rondeaul]

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?

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

<colgroup><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

 

[SpillerBD]

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.

 

[rondeaul]

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.

 

[SpillerBD]

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

=MIN(IF($A$2:$A$19=A2,$C$2:$C$19))

Use CTRL+SHIFT+ENTER to enter formula as Array Formula.

 

[Peter_SSs]

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
	A	B	C	D	E
1	Client Name	Status	Order Date	Client #
2	client 12	Active	17/05/2018	1620858	1
3	Client 1	Active	6/01/2018	1628914	2
4	client 10	Hold	6/05/2018	1630746	3
5	client 4	Hold	6/05/2018	1630546	4
6	client 9	Hold	6/05/2018	1630410	5
7	client 7	Active	6/07/2018	1479678	6
8	Client 2	Hold	6/11/2018	1634284	7
9	Client 1	Active	23/06/2018	1628914	2
10	client 10	Hold	23/06/2018	1630746	3
11	client 11	Rolled	23/06/2018	128596	10
12	client 12	Active	23/06/2018	1620858	1
13	Client 2	Hold	23/06/2018	1634284	7
14	client 4	Hold	23/06/2018	1630546	4
15	client 8	Active	23/06/2018	632193	14
16	client 3	Active	8/02/2018	1639915	15
17	client 5	Active	8/08/2018	1623580	16
18	client 3	Active	9/05/2018	1639915	15
19	client 6	Active	9/07/2018	1589648	18
Sort

After sorting:

Excel Workbook
	A	B	C	D	E
1	Client Name	Status	Order Date	Client #
2	client 12	Active	17/05/2018	1620858	1
3	client 12	Active	23/06/2018	1620858	1
4	Client 1	Active	6/01/2018	1628914	3
5	Client 1	Active	23/06/2018	1628914	3
6	client 10	Hold	6/05/2018	1630746	5
7	client 10	Hold	23/06/2018	1630746	5
8	client 4	Hold	6/05/2018	1630546	7
9	client 4	Hold	23/06/2018	1630546	7
10	client 9	Hold	6/05/2018	1630410	9
11	client 7	Active	6/07/2018	1479678	10
12	Client 2	Hold	6/11/2018	1634284	11
13	Client 2	Hold	23/06/2018	1634284	11
14	client 11	Rolled	23/06/2018	128596	13
15	client 8	Active	23/06/2018	632193	14
16	client 3	Active	8/02/2018	1639915	15
17	client 3	Active	9/05/2018	1639915	15
18	client 5	Active	8/08/2018	1623580	17
19	client 6	Active	9/07/2018	1589648	18
Sort

 

[rondeaul]

This worked perfectly. It was exactly what I needed. Thanks Peter!

 

[rondeaul]

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.

 

[SpillerBD]

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.

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....