formula for checking and write when a value last time appeared?

[excelNewbie22]

hi,
is it possible by formula to check and write when the last time a number/value repeated?

example:

test
	A	B	C	D
1	date	#	last time appeared	before last time
2	15/04/2022	6862	02/04/2022	24/03/2022
3	14/04/2022	8336	x	x
4	13/04/2022	5741	x	x
5	12/04/2022	6324	x	x
6	11/04/2022	5414	27/03/2022	x
7	10/04/2022	3336	x	x
8	09/04/2022	7384	x	x
9	08/04/2022	1315	x	x
10	07/04/2022	2172	x	x
11	06/04/2022	6617	x	x
12	05/04/2022	3323	x	x
13	04/04/2022	6728	x	x
14	03/04/2022	5738	x	x
15	02/04/2022	6862	x	x
16	01/04/2022	6483	x	x
17	31/03/2022	3172	x	x
18	30/03/2022	5582	x	x
19	29/03/2022	8648	x	x
20	28/03/2022	6452	x	x
21	27/03/2022	5414	x	x
22	26/03/2022	4647	x	x
23	25/03/2022	5575	x	x
24	24/03/2022	6862	x	x
test

 

[JamesCanale]

MrExcelPlayground8.xlsx
	A	B	C	D
1	date	#	last time appeared	before last time
2	4/15/2022	6862	4/2/2022	3/24/2022
3	4/14/2022	8336
4	4/13/2022	5741
5	4/12/2022	6324
6	4/11/2022	5414	3/27/2022
7	4/10/2022	3336
8	4/9/2022	7384
9	4/8/2022	1315
10	4/7/2022	2172
11	4/6/2022	6617
12	4/5/2022	3323
13	4/4/2022	6728
14	4/3/2022	5738
15	4/2/2022	6862
16	4/1/2022	6483
17	3/31/2022	3172
18	3/30/2022	5582
19	3/29/2022	8648
20	3/28/2022	6452
21	3/27/2022	5414
22	3/26/2022	4647
23	3/25/2022	5575
24	3/24/2022	6862
Sheet3

Cell Formulas
Range	Formula
C2:C24	C2	=IF(ISNA(MATCH(B2,B$1:B1,0)),IFNA(INDEX(A3:A$25,MATCH(B2,B3:B$25,0)),""),"")
D2:D24	D2	=IF(ISNA(MATCH(B2,B$1:B1,0)),IFNA(INDEX(A2:A$25,MATCH(B2,B3:B$25,0)+MATCH(B2,OFFSET(B2,MATCH(B2,B3:B$25,0)+1,0,ROWS(A2:A$24)-MATCH(B2,B3:B$25,0)-1),0)+1),""),"")

 

[DanteAmor]

How about:

Dante Amor
	A	B	C	D
1	date	#	last time appeared	before last time
2	15/04/2022	6862	02/04/2022	24/03/2022
3	14/04/2022	8336	x	x
4	13/04/2022	5741	09/04/2022	03/04/2022
5	12/04/2022	6324	x	x
6	11/04/2022	5414	27/03/2022	x
7	10/04/2022	3336	x	x
8	09/04/2022	5741	x	x
9	08/04/2022	1315	x	x
10	07/04/2022	2172	x	x
11	06/04/2022	6617	x	x
12	05/04/2022	3323	x	x
13	04/04/2022	6728	x	x
14	03/04/2022	5741	x	x
15	02/04/2022	6862	x	x
16	01/04/2022	6483	x	x
17	31/03/2022	3172	x	x
18	30/03/2022	5582	x	x
19	29/03/2022	8648	x	x
20	28/03/2022	6452	x	x
21	27/03/2022	5414	x	x
22	26/03/2022	4647	x	x
23	25/03/2022	5741	x	x
24	24/03/2022	6862	x	x
Hoja1

Cell Formulas
Range	Formula
C2:D24	C2	=IF(AND(COUNTIF($B$2:$B$24,$B2)>COLUMNS($C$1:C$1),COUNTIF($B$1:$B1,$B2)=0),LARGE(INDEX(($B2=$B$2:$B$24)*$A$2:$A$24,),COLUMNS($C$1:C$1)+1),"x")

 

[excelNewbie22]

JamesCanale:
it works fine but after i apply the formula to the all column and try to modify a cell, for example b11 to match b9, the formula in c9 do not change

test.xlsx
	A	B	C	D
1	date	#	last time appeared	before last time
2	15/04/2022	6862	02/04/2022	24/03/2022
3	14/04/2022	8336
4	13/04/2022	5741
5	12/04/2022	6324
6	11/04/2022	5414	27/03/2022
7	10/04/2022	3336
8	09/04/2022	7384
9	08/04/2022	1315
10	07/04/2022	2172
11	06/04/2022	1315
12	05/04/2022	3323
13	04/04/2022	6728
14	03/04/2022	5738
15	02/04/2022	6862
16	01/04/2022	6483
17	31/03/2022	3172
18	30/03/2022	5582
19	29/03/2022	8648
20	28/03/2022	6452
21	27/03/2022	5414
22	26/03/2022	4647
23	25/03/2022	5575
24	24/03/2022	6862
test

Cell Formulas
Range	Formula
C2:C24	C2	=IF(ISNA(MATCH(B2,B$1:B1,0)),IFNA(INDEX(A3:A$25,MATCH(B2,B3:B$25,0)),""),"")
D2:D24	D2	=IF(ISNA(MATCH(B2,B$1:B1,0)),IFNA(INDEX(A2:A$25,MATCH(B2,B3:B$25,0)+MATCH(B2,OFFSET(B2,MATCH(B2,B3:B$25,0)+1,0,ROWS(A2:A$24)-MATCH(B2,B3:B$25,0)-1),0)+1),""),"")

DanteAmor:
it works good as well but some of the dates are wrong

test.xlsx
	A	B	C	D
1	date	#	last time appeared	before last time
2	15/04/2022	6862	02/04/2022	24/03/2022
3	14/04/2022	8336	x	x
4	13/04/2022	5741	x	x
5	12/04/2022	6324	x	x
6	11/04/2022	5414	27/03/2022	x
7	10/04/2022	3336	x	x
8	09/04/2022	7384	x	x
9	08/04/2022	1315	00/01/1900	x
10	07/04/2022	2172	x	x
11	06/04/2022	1315	x	x
12	05/04/2022	3323	x	x
13	04/04/2022	6728	x	x
14	03/04/2022	5738	x	x
15	02/04/2022	6862	x	x
16	01/04/2022	6483	x	x
17	31/03/2022	3172	x	x
18	30/03/2022	5582	x	x
19	29/03/2022	8648	x	x
20	28/03/2022	6452	x	x
21	27/03/2022	5414	x	x
22	26/03/2022	4647	x	x
23	25/03/2022	5575	x	x
24	24/03/2022	6862	x	x
test

Cell Formulas
Range	Formula
C2:D24	C2	=IF(AND(COUNTIF($B$2:$B$24,$B2)>COLUMNS($C$1:C$1),COUNTIF($B$1:$B1,$B2)=0),LARGE(INDEX(($B2=$B$2:$B$24)*$A$2:$A$24,),COLUMNS($C$1:C$1)+1),"x")

 

[JamesCanale]

I made that change on mine and it worked:

MrExcelPlayground8.xlsx
	A	B	C	D
1	date	#	last time appeared	before last time
2	4/15/2022	6862	4/2/2022	3/24/2022
3	4/14/2022	8336
4	4/13/2022	5741
5	4/12/2022	6324
6	4/11/2022	5414	3/27/2022
7	4/10/2022	3336
8	4/9/2022	7384
9	4/8/2022	1315	4/6/2022
10	4/7/2022	2172
11	4/6/2022	1315
12	4/5/2022	3323
13	4/4/2022	6728
14	4/3/2022	5738
15	4/2/2022	6862
16	4/1/2022	6483
17	3/31/2022	3172
18	3/30/2022	5582
19	3/29/2022	8648
20	3/28/2022	6452
21	3/27/2022	5414
22	3/26/2022	4647
23	3/25/2022	5575
24	3/24/2022	6862
Sheet3

Cell Formulas
Range	Formula
C2:C24	C2	=IF(ISNA(MATCH(B2,B$1:B1,0)),IFNA(INDEX(A3:A$25,MATCH(B2,B3:B$25,0)),""),"")
D2:D24	D2	=IF(ISNA(MATCH(B2,B$1:B1,0)),IFNA(INDEX(A2:A$25,MATCH(B2,B3:B$25,0)+MATCH(B2,OFFSET(B2,MATCH(B2,B3:B$25,0)+1,0,ROWS(A2:A$24)-MATCH(B2,B3:B$25,0)-1),0)+1),""),"")

 

[DanteAmor]

DanteAmor:
it works good as well but some of the dates are wrong

I'm testing with the same data and I'm having no problems:

Dante Amor
	A	B	C	D
1	date	#	last time appeared	before last time
2	15/04/2022	6862	02/04/2022	24/03/2022
3	14/04/2022	8336	x	x
4	13/04/2022	5741	x	x
5	12/04/2022	6324	x	x
6	11/04/2022	5414	27/03/2022	x
7	10/04/2022	3336	x	x
8	09/04/2022	7384	x	x
9	08/04/2022	1315	06/04/2022	x
10	07/04/2022	2172	x	x
11	06/04/2022	1315	x	x
12	05/04/2022	3323	x	x
13	04/04/2022	6728	x	x
14	03/04/2022	5738	x	x
15	02/04/2022	6862	x	x
16	01/04/2022	6483	x	x
17	31/03/2022	3172	x	x
18	30/03/2022	5582	x	x
19	29/03/2022	8648	x	x
20	28/03/2022	6452	x	x
21	27/03/2022	5414	x	x
22	26/03/2022	4647	x	x
23	25/03/2022	5575	x	x
24	24/03/2022	6862	x	x
Hoja1

Cell Formulas
Range	Formula
C2:D24	C2	=IF(AND(COUNTIF($B$2:$B$24,$B2)>COLUMNS($C$1:C$1),COUNTIF($B$1:$B1,$B2)=0),LARGE(INDEX(($B2=$B$2:$B$24)*$A$2:$A$24,),COLUMNS($C$1:C$1)+1),"x")

Review the formula. If you have problems, comment on what date you have a problem with and what result it should have.

 

[excelNewbie22]

sorry for the late reply,
JamesCanale and DanteAmor
strange,
for both your formulas,
when trying to modify a number like at b9 it works and c9 change/date fixed
but if i try to modify the number at b11 first then c9 stays blank/date wrong
same goes for each number
i think it something with my excel file

so i test it with another
and all good
who do i give the feedback to?

 

[excelNewbie22]

by feedback i mean marking as solution,
i supposed i'll marked the first one
BUT BOTH OF YOU WORKS GOOD!!
T-H-A-N-K Y-O-U!

 

[DanteAmor]

Glad we could help & thanks for the feedback

 

[Peter_SSs]

Here are a couple of alternatives that I think also do what you want

22 04 17.xlsm
	A	B	C	D
1	date	#	last time appeared	before last time
2	15/04/2022	6862	02/04/2022	24/03/2022
3	14/04/2022	8336	x	x
4	13/04/2022	5741	x	x
5	12/04/2022	6324	x	x
6	11/04/2022	5414	27/03/2022	x
7	10/04/2022	3336	x	x
8	09/04/2022	7384	x	x
9	08/04/2022	1315	x	x
10	07/04/2022	2172	x	x
11	06/04/2022	6617	x	x
12	05/04/2022	3323	x	x
13	04/04/2022	6728	x	x
14	03/04/2022	5738	x	x
15	02/04/2022	6862	x	x
16	01/04/2022	6483	x	x
17	31/03/2022	3172	x	x
18	30/03/2022	5582	x	x
19	29/03/2022	8648	x	x
20	28/03/2022	6452	x	x
21	27/03/2022	5414	x	x
22	26/03/2022	4647	x	x
23	25/03/2022	5575	x	x
24	24/03/2022	6862	x	x
Last Time & Time Before

Cell Formulas
Range	Formula
C2:C24	C2	=IF(COUNTIF(B$2:B2,B2)=1,IFERROR(AGGREGATE(14,6,A3:A$100/(B3:B$100=B2),1),"x"),"x")
D2:D24	D2	=IF(C2="x","x",IFERROR(AGGREGATE(14,6,A3:A$100/(B3:B$100=B2),2),"x"))