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

Patient	ID	Location	Date of Fall	Type of Fall	Location
Skywalker, Luke	123456	Home	12/25/2017	Fall with Injury
Skywalker, Luke	123456	SNF/Nursing Home	12/28/2017	Fall with Injury
ID	Patient Last	Patient First	Admit date	TERMINATION_DATE	Start of Care	End of Care	dbo_RES_BASIC.ORGANIZATION_NAME	Location	Referral
234567	Ren	Kylo	12/24/2017		12/24/2017		Admitted-Home	South	Medical Hospital
123456	Skywalker	Luke	12/9/2017		12/18/2017	12/25/2017	Admitted-Home	North	Fowl Medical
123456	Skywalker	Luke	12/9/2017		12/26/2017	12/28/2017	Relocation-Home to Care Center	Central	Fowl Medical
123456	Skywalker	Luke	12/9/2017		12/29/2017		Relocation-Care Center to Facility	West	Fowl Medical
456789	Yoda	Mister	11/19/2017	12/18/2017	11/19/2017	12/12/2017	ReAdmit	South	Center Clinic
456789	Yoda	Mister	11/19/2017	12/18/2017	12/14/2017	12/18/2017	Discharge	South	Center Clinic

<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> </tbody>

 

[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))