Second to last order by client ID

[UserI]

Hi,

A have a list with different clients with their orders effective date and the items they purchased. Each client is in the file at least 2-3 times.

Column A = client ID
Column B = order effective date
Column C = item

Based on this data, I need to have in another sheet unique client IDs, each with their second to last order.
What formula can help with this task?

Thank you for your help.
Regards.

 

[Fluff]

Can you post some sample data & expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[UserI]

Client data.xlsx
	G
8
Data

 

[Fluff]

You need to select the range of cells, not just one.

 

[UserI]

You need to select the range of cells, not just one.

Client data.xlsx
	A	B	C
1	Customer ID	Order date	Item
2	C200	01-03-21	Printer paper
3	C201	02-03-21	Three-hole punched paper
4	C201	03-03-21	Graph paper
5	C201	04-03-21	Tracing paper
6	C201	05-03-21	Carbon paper
7	C202	03-03-21	Color card stock
8	C203	04-03-21	Heavy-duty card stock
9	C204	05-03-21	Wrapping paper
10	C205	06-03-21	Greeting cards and envelopes
11	C206	07-03-21	Business cards
12	C205	08-03-21	Color card stock
13	C206	09-03-21	Heavy-duty card stock
14	C211	10-03-21	Wrapping paper
15	C207	08-03-21	Letterhead
16	C208	09-03-21	Poster board
17	C209	10-03-21	Letterhead
18	C210	11-03-21	Letterhead
19	C211	12-03-21	Letterhead
20	C212	13-03-21	Poster board
21	C213	14-03-21	Graph paper
22	C214	15-03-21	Tracing paper
23	C215	16-03-21	Carbon paper
24	C216	17-03-21	Graph paper
25	C217	18-03-21	Tracing paper
Data

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Customer ID	Order date	Item
2	C200	01/03/2021	Printer paper				C200	Printer paper
3	C200	02/03/2021	Three-hole punched paper				C201	Tracing paper
4	C201	03/03/2021	Graph paper				C202	Heavy-duty card stock
5	C201	04/03/2021	Tracing paper				C205	Greeting cards and envelopes
6	C201	05/03/2021	Carbon paper				C206	Business cards
7	C202	03/03/2021	Color card stock				C211	Wrapping paper
8	C202	04/03/2021	Heavy-duty card stock				C207	Poster board
9	C202	05/03/2021	Wrapping paper				C210	Poster board
10	C205	06/03/2021	Greeting cards and envelopes				C213	Graph paper
11	C206	07/03/2021	Business cards				C214	Tracing paper
12	C205	08/03/2021	Color card stock
13	C206	09/03/2021	Heavy-duty card stock
14	C211	10/03/2021	Wrapping paper
15	C207	08/03/2021	Letterhead
16	C207	09/03/2021	Poster board
17	C207	10/03/2021	Letterhead
18	C210	11/03/2021	Letterhead
19	C211	12/03/2021	Letterhead
20	C210	13/03/2021	Poster board
21	C213	14/03/2021	Graph paper
22	C214	15/03/2021	Tracing paper
23	C213	16/03/2021	Carbon paper
24	C214	17/03/2021	Graph paper
25	C210	18/03/2021	Tracing paper
26
Master

Cell Formulas
Range	Formula
G2:G11	G2	=UNIQUE(FILTER(A2:A100,A2:A100<>""))
H2:H11	H2	=CHOOSEROWS(FILTER($C$2:$C$100,$A$2:$A$100=G2),-2)
Dynamic array formulas.

 

[UserI]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Customer ID	Order date	Item
2	C200	01/03/2021	Printer paper				C200	Printer paper
3	C200	02/03/2021	Three-hole punched paper				C201	Tracing paper
4	C201	03/03/2021	Graph paper				C202	Heavy-duty card stock
5	C201	04/03/2021	Tracing paper				C205	Greeting cards and envelopes
6	C201	05/03/2021	Carbon paper				C206	Business cards
7	C202	03/03/2021	Color card stock				C211	Wrapping paper
8	C202	04/03/2021	Heavy-duty card stock				C207	Poster board
9	C202	05/03/2021	Wrapping paper				C210	Poster board
10	C205	06/03/2021	Greeting cards and envelopes				C213	Graph paper
11	C206	07/03/2021	Business cards				C214	Tracing paper
12	C205	08/03/2021	Color card stock
13	C206	09/03/2021	Heavy-duty card stock
14	C211	10/03/2021	Wrapping paper
15	C207	08/03/2021	Letterhead
16	C207	09/03/2021	Poster board
17	C207	10/03/2021	Letterhead
18	C210	11/03/2021	Letterhead
19	C211	12/03/2021	Letterhead
20	C210	13/03/2021	Poster board
21	C213	14/03/2021	Graph paper
22	C214	15/03/2021	Tracing paper
23	C213	16/03/2021	Carbon paper
24	C214	17/03/2021	Graph paper
25	C210	18/03/2021	Tracing paper
26
Master

Cell Formulas
Range	Formula
G2:G11	G2	=UNIQUE(FILTER(A2:A100,A2:A100<>""))
H2:H11	H2	=CHOOSEROWS(FILTER($C$2:$C$100,$A$2:$A$100=G2),-2)
Dynamic array formulas.

Thank you.
What would be the formula if I need the second to last order date for each client?

 

[Fluff]

Do you mean like

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Customer ID	Order date	Item
2	C200	01/03/2021	Printer paper				C200	01/03/2021	Printer paper
3	C200	02/03/2021	Three-hole punched paper				C201	04/03/2021	Tracing paper
4	C201	03/03/2021	Graph paper				C202	04/03/2021	Heavy-duty card stock
5	C201	04/03/2021	Tracing paper				C205	06/03/2021	Greeting cards and envelopes
6	C201	05/03/2021	Carbon paper				C206	07/03/2021	Business cards
7	C202	03/03/2021	Color card stock				C211	10/03/2021	Wrapping paper
8	C202	04/03/2021	Heavy-duty card stock				C207	09/03/2021	Poster board
9	C202	05/03/2021	Wrapping paper				C210	13/03/2021	Poster board
10	C205	06/03/2021	Greeting cards and envelopes				C213	14/03/2021	Graph paper
11	C206	07/03/2021	Business cards				C214	15/03/2021	Tracing paper
12	C205	08/03/2021	Color card stock
13	C206	09/03/2021	Heavy-duty card stock
14	C211	10/03/2021	Wrapping paper
15	C207	08/03/2021	Letterhead
16	C207	09/03/2021	Poster board
17	C207	10/03/2021	Letterhead
18	C210	11/03/2021	Letterhead
19	C211	12/03/2021	Letterhead
20	C210	13/03/2021	Poster board
21	C213	14/03/2021	Graph paper
22	C214	15/03/2021	Tracing paper
23	C213	16/03/2021	Carbon paper
24	C214	17/03/2021	Graph paper
25	C210	18/03/2021	Tracing paper
26
Master

Cell Formulas
Range	Formula
G2:G11	G2	=UNIQUE(FILTER(A2:A100,A2:A100<>""))
H2:I11	H2	=CHOOSEROWS(FILTER($B$2:$C$100,$A$2:$A$100=G2),-2)
Dynamic array formulas.