Help with linking events in one table to specific location in another table

[dennisridgley]

Good morning!

We are a healthcare provider and need to track where falls occur. Patients can be in one or more locations and have one or more falls in a perod. I've been trying to use INDEX/MATCH functions to identify the location for each fall, by first identiying the patient by their ID (uique identifier) from the first table to the second table then by the date range, but can't get it done. In the example below, Mr. Skywalker has two falls. The first on 12/25 should have a locatio of "North" the second on 12/28 should have the location of "Central". I'm new to VBA which is why I'm trying do do Excel functions, but a Macro that solves this will be welcome as well. Thanks for your help!! - Dennis

[TABLE="width: 632"]
<colgroup><col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3949;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;" span="2"> <col width="64" style="width: 48pt;" span="2"> <col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <tbody>[TR]
[TD="width: 108, bgcolor: transparent"]Patient[/TD]
[TD="width: 75, bgcolor: transparent"]ID[/TD]
[TD="width: 110, bgcolor: transparent"]Location[/TD]
[TD="width: 73, bgcolor: transparent"]Date of Fall[/TD]
[TD="width: 90, bgcolor: transparent"]Type of Fall[/TD]
[TD="width: 73, bgcolor: transparent"]Location[/TD]
[TD="width: 73, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 111, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Skywalker, Luke[/TD]
[TD="bgcolor: transparent"]123456[/TD]
[TD="bgcolor: transparent"]Home[/TD]
[TD="bgcolor: transparent"]12/25/2017[/TD]
[TD="bgcolor: transparent"]Fall with Injury [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Skywalker, Luke[/TD]
[TD="bgcolor: transparent"]123456[/TD]
[TD="bgcolor: transparent"]SNF/Nursing Home[/TD]
[TD="bgcolor: transparent"]12/28/2017[/TD]
[TD="bgcolor: transparent"]Fall with Injury [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ID[/TD]
[TD="bgcolor: transparent"]Patient Last[/TD]
[TD="bgcolor: transparent"]Patient First[/TD]
[TD="bgcolor: transparent"]Admit date[/TD]
[TD="bgcolor: transparent"]TERMINATION_DATE[/TD]
[TD="bgcolor: transparent"]Start of Care[/TD]
[TD="bgcolor: transparent"]End of Care[/TD]
[TD="bgcolor: transparent"]dbo_RES_BASIC.ORGANIZATION_NAME[/TD]
[TD="bgcolor: transparent"]Location[/TD]
[TD="bgcolor: transparent"]Referral[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]234567[/TD]
[TD="bgcolor: transparent"]Ren[/TD]
[TD="bgcolor: transparent"]Kylo[/TD]
[TD="bgcolor: transparent"]12/24/2017[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]12/24/2017[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Admitted-Home[/TD]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent"]Medical Hospital[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]123456[/TD]
[TD="bgcolor: transparent"]Skywalker[/TD]
[TD="bgcolor: transparent"]Luke[/TD]
[TD="bgcolor: transparent"]12/9/2017[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]12/18/2017[/TD]
[TD="bgcolor: transparent"]12/25/2017[/TD]
[TD="bgcolor: transparent"]Admitted-Home[/TD]
[TD="bgcolor: transparent"]North[/TD]
[TD="bgcolor: transparent"]Fowl Medical[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]123456[/TD]
[TD="bgcolor: transparent"]Skywalker[/TD]
[TD="bgcolor: transparent"]Luke[/TD]
[TD="bgcolor: transparent"]12/9/2017[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]12/26/2017[/TD]
[TD="bgcolor: transparent"]12/28/2017[/TD]
[TD="bgcolor: transparent"]Relocation-Home to Care Center[/TD]
[TD="bgcolor: transparent"]Central[/TD]
[TD="bgcolor: transparent"]Fowl Medical[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]123456[/TD]
[TD="bgcolor: transparent"]Skywalker[/TD]
[TD="bgcolor: transparent"]Luke[/TD]
[TD="bgcolor: transparent"]12/9/2017[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]12/29/2017[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Relocation-Care Center to Facility[/TD]
[TD="bgcolor: transparent"]West[/TD]
[TD="bgcolor: transparent"]Fowl Medical[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]456789[/TD]
[TD="bgcolor: transparent"]Yoda[/TD]
[TD="bgcolor: transparent"]Mister[/TD]
[TD="bgcolor: transparent"]11/19/2017[/TD]
[TD="bgcolor: transparent"]12/18/2017[/TD]
[TD="bgcolor: transparent"]11/19/2017[/TD]
[TD="bgcolor: transparent"]12/12/2017[/TD]
[TD="bgcolor: transparent"]ReAdmit[/TD]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent"]Center Clinic[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]456789[/TD]
[TD="bgcolor: transparent"]Yoda[/TD]
[TD="bgcolor: transparent"]Mister[/TD]
[TD="bgcolor: transparent"]11/19/2017[/TD]
[TD="bgcolor: transparent"]12/18/2017[/TD]
[TD="bgcolor: transparent"]12/14/2017[/TD]
[TD="bgcolor: transparent"]12/18/2017[/TD]
[TD="bgcolor: transparent"]Discharge[/TD]
[TD="bgcolor: transparent"]South[/TD]
[TD="bgcolor: transparent"]Center Clinic[/TD]
[/TR]
</tbody>[/TABLE]

 

[RasGhul]

Hi Dennis,

Maybe try;

Book1
	A	B	C	D	E	F	G	H	I
1	ID	Patient Last	Patient First	Admit date	TERMINATION_DATE	Start of Care	End of Care		Location
2	234567	Ren	Kylo	12/24/2017		12/24/2017		Admitted-Home	South
3	123456	Skywalker	Luke	12/09/2017		12/18/2017	12/25/2017	Admitted-Home	North
4	123456	Skywalker	Luke	12/09/2017		12/26/2017	12/28/2017	Relocation-Home to Care Center	Central
5	123456	Skywalker	Luke	12/09/2017		12/29/2017		Relocation-Care Center to Facility	West
6	456789	Yoda	Mister	11/19/2017	12/18/2017	11/19/2017	12/12/2017	ReAdmit	South
7	456789	Yoda	Mister	11/19/2017	12/18/2017	12/14/2017	12/18/2017	Discharge	South
8
9
10	Patient	ID	Location	Date of Fall	Type of Fall	Location
11	Skywalker, Luke	123456	Home	12/25/2017	Fall with Injury	North
12	Skywalker, Luke	123456	SNF/Nursing Home	12/28/2017	Fall with Injury	Central
Sheet1

Cell Formulas
Range	Formula
F11	=INDEX($I$2:$I$7,MATCH(B11&D11,INDEX($A$2:$A$7&$G$2:$G$7,),0))