Help with transposing matching data between tabs

[Shilla]

Let me be honest: I'm not as good at Excel as I thought I was before finding this forum. You folks are amazing. I got some help making this in the past, so I'm not even as skilled as my attached spreadsheet would imply.

I'm trying to create a spreadsheet that a receptionist would use at a front desk. The tricky part is I want it to automatically track each ID's (column B) 6 most recent occurrences (appointments) from Column D. I have a separate tab that lists appointments for each unique ID (from Column B), and gives me the 6 most recent appointments. But I'm not sure how to match it up so that it's all on the same page and tied in so that it could be sorted if needed.

If anyone has any ideas, please let me know.
Here's a link to the spreadsheet:

Get Advice.xlsx

docs.google.com

 

[Peter_SSs]

I'm not at all sure just what final format you are wanting or on which sheet.
If this is not the sort of thing you are after then please fill in the desired results manually and post that again with further explanation.

BTW, many of the helpers here choose not to download files from other sites or, due to security restrictions at their workplace, are unable to download such files.
You will generally get more potential helpers if you explain your problem clearly in words and, if needed, post a small (copyable) screen shot or two directly in your post. My signature block at the bottom of this post has help regarding that - see the XL2BB link.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page.)

Shilla_1.xlsx
	B		D					I	J	K	L	M	N
1	Patient ID		Appointment Date (Today)					Newest Appointment	Previous 2	Previous 3	Previous 4	Previous 5	Previous 6
2	1122		2/04/2025					2/04/2025	1/04/2025	5/03/2025	2/03/2025	1/03/2025	25/02/2025
3	1122		1/04/2025					2/04/2025	1/04/2025	5/03/2025	2/03/2025	1/03/2025	25/02/2025
4	1122		1/03/2025					2/04/2025	1/04/2025	5/03/2025	2/03/2025	1/03/2025	25/02/2025
5	1122		5/03/2025					2/04/2025	1/04/2025	5/03/2025	2/03/2025	1/03/2025	25/02/2025
6	1123		4/04/2025					4/04/2025
7	1124		4/04/2025					4/04/2025	18/02/2025
8	1125		5/04/2025					5/04/2025
9	1122		2/03/2025					2/04/2025	1/04/2025	5/03/2025	2/03/2025	1/03/2025	25/02/2025
10	1122		25/02/2025					2/04/2025	1/04/2025	5/03/2025	2/03/2025	1/03/2025	25/02/2025
11	1124		18/02/2025					4/04/2025	18/02/2025
12	1122		15/01/2025					2/04/2025	1/04/2025	5/03/2025	2/03/2025	1/03/2025	25/02/2025
13
14
Log

Cell Formulas
Range	Formula
I2:I14	I2	=LET(d,MAXIFS([Appointment Date (Today)],[Patient ID],[@[Patient ID]]),IF(d=0,"",d))
J2:J14	J2	=LET(d,MAXIFS([Appointment Date (Today)],[Patient ID],[@[Patient ID]],[Appointment Date (Today)],"<"&[@[Newest Appointment]]),IF(d=0,"",d))
K2:K14	K2	=LET(d,MAXIFS([Appointment Date (Today)],[Patient ID],[@[Patient ID]],[Appointment Date (Today)],"<"&[@[Previous 2]]),IF(d=0,"",d))
L2:L14	L2	=LET(d,MAXIFS([Appointment Date (Today)],[Patient ID],[@[Patient ID]],[Appointment Date (Today)],"<"&[@[Previous 3]]),IF(d=0,"",d))
M2:M14	M2	=LET(d,MAXIFS([Appointment Date (Today)],[Patient ID],[@[Patient ID]],[Appointment Date (Today)],"<"&[@[Previous 4]]),IF(d=0,"",d))
N2:N14	N2	=LET(d,MAXIFS([Appointment Date (Today)],[Patient ID],[@[Patient ID]],[Appointment Date (Today)],"<"&[@[Previous 5]]),IF(d=0,"",d))

 

[Shilla]

Yes I understand about the Drive issue. My issue is that I'm also limited by workplace IT policy and permissions. It was all I could come up with on the fly.

You, however, translated my garbled mess into beauty! Thank you for working that out. That worked perfectly! Color me impressed. Or conditional format me impressed, give that this is an Excel forum.

 

[Peter_SSs]

My issue is that I'm also limited by workplace IT policy and permissions.

Fair enough. In any future thread you start I suggest you mention that in post 1 so we don't keep asking.

Thank you for working that out. That worked perfectly!

Good news and you are welcome. Thanks for the follow-up.

 

[Shilla]

Will do on future posts. I was thinking that was just commonplace and understood nowadays. But you’re correct. I left a lot to the imagination. My apologies and appreciation to you and the forum members.