Tracking client dates attended from adjacent sheet

[Shilla]

I'm working on a spreadsheet to track the clients seen in our facility. I have one in which every client will be entered, including recurring clients. This is a continuously used spreadsheet that will not have data deleted.

I want a second sheet that references the first, removes the duplicates, but enters the subsequent visit dates in order in each column.
So as you see, patient 533 above would only be entered once, but the visits would be entered across the row for each visit.

I'm sure this isn't as hard as I'm making it, but it's time to ask for help after fighting with
it for a few hours.
Any help is greatly appreciated.

 

[Shilla]

In case clarification is needed:
As someone enters a patient number in the first sheet (Log!), it would be pulled over to the second sheet (Tracker!). Along with the date of service. However, if that patient number is a duplicate, it would only pull over the date of service, placing it in order in the row of entries for that patient.

I will be doing other things with the sheet. This is used to give service providers a quick visual reference to see the patient if the patient is either A)seen twice in a 30-60 day timespan or remove the patient if they B) are not seen within that window of time.
I can manage that part with simple formulas and conditional formatting (I assume). But gathering the data without them having to type into two sheets is vital to this process.

 

[AhoyNC]

Maybe something like this:

Sheet 1

Book2
	A	B
1
2
3
4	Pat. ID	Date
5	544	6/6/2022
6	533	11/1/2022
7	522	11/3/2022
8	533	11/7/2022
Sheet1

Sheet2

Book2
	A	B	C	D	E	F	G	H
1
2
3
4	Pt#	Csir	PS	Date	Date	Date	Date	Date
5	544			6/6/2022
6	533			11/1/2022	11/7/2022
7	522			11/3/2022
8
Sheet2

Cell Formulas
Range	Formula
A5:A7	A5	=UNIQUE(Sheet1!$A$5:$A$8)
D5,D7,D6:E6	D5	=TRANSPOSE(FILTER(Sheet1!B$5:B$8,Sheet1!$A$5:$A$8=Sheet2!A5,""))
Dynamic array formulas.

 

[SpillerBD]

You could add a SORT function too, especially on the dates just to make sure things stay in a particular order.

 

[Shilla]

Maybe something like this:

Sheet 1

Book2
	A	B
1
2
3
4	Pat. ID	Date
5	544	6/6/2022
6	533	11/1/2022
7	522	11/3/2022
8	533	11/7/2022
Sheet1

Sheet2

Book2
	A	B	C	D	E	F	G	H
1
2
3
4	Pt#	Csir	PS	Date	Date	Date	Date	Date
5	544			6/6/2022
6	533			11/1/2022	11/7/2022
7	522			11/3/2022
8
Sheet2

Cell Formulas
Range	Formula
A5:A7	A5	=UNIQUE(Sheet1!$A$5:$A$8)
D5,D7,D6:E6	D5	=TRANSPOSE(FILTER(Sheet1!B$5:B$8,Sheet1!$A$5:$A$8=Sheet2!A5,""))
Dynamic array formulas.

That worked perfectly! Thank you!

 

[Shilla]

You could add a SORT function too, especially on the dates just to make sure things stay in a particular order.

Great point. Thank you for the reminder!

 

[AhoyNC]

You're welcome thanks for the feedback.