Search for date and penultimate date and numbers

[Wojciech]

Hi,
please help me write the formula. I am attaching a screenshot that shows the table and solution. The formula should be filled with the AAA or BBB (A13 and A14) and the formula should find the latest date (B13 and B14) and the number (number of kilometers) assigned to it (C13 and C14). The formula in D13,D14 finds the penultimate date and in E13 and E14 penultimate date number assigned to the dates.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Wojciech]

I use office 2011 with office 365 features. I updated my profile information. Thank you for your attention.

ps. I tried to install XL2BB but got the message "This file type is not supported in Protected View".

 

[HighAndWilder]

Hi,
please help me write the formula. I am attaching a screenshot that shows the table and solution. The formula should be filled with the AAA or BBB (A13 and A14) and the formula should find the latest date (B13 and B14) and the number (number of kilometers) assigned to it (C13 and C14). The formula in D13,D14 finds the penultimate date and in E13 and E14 penultimate date number assigned to the dates.

Does this work for you?

Solution for 365.

Search for date and penultimate date and numbers.xlsm
	A	B	C	D	E
1	Name	Date	KM
2	AAA	28/01/2023	19469
3	AAA	01/05/2023	19594
4	AAA	26/02/2023	9030
5	AAA	21/09/2023	8781
6	BBB	04/05/2023	8528
7	BBB	12/05/2023	19385
8	BBB	19/05/2023	14976
9	BBB	02/07/2023	11799
10
11		Solution
12	inquiry:	Last		Second to last
13	AAA	28/01/2023	19469	26/02/2023	9030
14	BBB	04/05/2023	8528	12/05/2023	19385
Sheet1

Cell Formulas
Range	Formula
B13:C14	B13	=CHOOSEROWS(SORT(FILTER($B$2:$C$9,$A$2:$A$9=$A13)),1)
D13:E14	D13	=CHOOSEROWS(SORT(FILTER($B$2:$C$9,$A$2:$A$9=$A13)),2)
Dynamic array formulas.

 

[Fluff]

Thanks for that, how about

Fluff.xlsm
	A	B	C	D	E
1	Name	Date	KM
2	AAA	07/09/2023	30019
3	AAA	19/09/2023	31369
4	AAA	20/10/2023	33293
5	AAA	01/09/2023	27579
6	BBB	27/07/2023	24972
7	BBB	04/08/2023	25695
8	BBB	07/07/2023	23392
9
10
11		Solution
12	inquiry:	Last		Second to last
13	AAA	20/10/2023	33293	19/09/2023	31369
14	BBB	04/08/2023	25695	27/07/2023	24972
Sheet6

Cell Formulas
Range	Formula
B13:E14	B13	=TOROW(TAKE(SORT(FILTER($B$2:$C$8,$A$2:$A$8=A13),1,-1),2))
Dynamic array formulas.

 

[Fluff]

ps. I tried to install XL2BB but got the message "This file type is not supported in Protected View".

In that case have a look here XL2BB Icons greyed out

 

[Wojciech]

I may have made a mistake in saying that I have Office 2021 with Office 365 features. It looks like I don't have the latest features available. I don't really understand why (I'm attaching a screenshot of my version). In my language version on the Microsoft website I see the correct translation of the functions for office 365, but my package does not see them.

I would like to ask you to propose a solution for Office 2021.

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E
1	Name	Date	KM
2	AAA	07/09/2023	30019
3	AAA	19/09/2023	31369
4	AAA	20/10/2023	33293
5	AAA	01/09/2023	27579
6	BBB	27/07/2023	24972
7	BBB	04/08/2023	25695
8	BBB	07/07/2023	23392
9
10
11		Solution
12	inquiry:	Last		Second to last
13	AAA	20/10/2023	33293	19/09/2023	31369
14	BBB	04/08/2023	25695	27/07/2023	24972
Sheet6

Cell Formulas
Range	Formula
B13:E14	B13	=INDEX(SORT(FILTER($B$2:$C$8,$A$2:$A$8=A13),1,-1),{1,1,2,2},{1,2,1,2})
Dynamic array formulas.

 

[Wojciech]

Thank you. It's work fine now. What does it mean {1,1,2,2}. The second part {1,2,1,2} I supposed it mean B and C and B and C columns.

 

[Fluff]

The {1,1,2,2} is the row element & {1,2,1,2} is the column element. So it's taking row 1 col 1, then row 1 col 2, row 2 col 1 & finally row 2 col 2