Trying to combine data from two Excel sheets

[alex_345]

Hi there,
I have a bit of a tricky thing I'm trying to accomplish (to me, anyways); maybe the Excel wizards have an idea that would work for me?

I have a software that exports reports for registrations of a program; the software creates one report that has the attendees listed (they are listed multiple times if they register for multiple weeks at the program) and another report with their contact and emergency contact information (but not who the info is for—idk why it's like this lol). I'm trying to create one master list with all the attendees and their contact and any emergency contact info for them, one that actually tells me who these contacts are for. This is proving a challenge for me.

So the registrations and contacts reports have a "Registration ID" in common. But the registrations report can have multiple registrations under the same ID if the person signs up for multiple weeks, and the contacts report can have multiple results for the same Registration ID if multiple contacts were inputted; each contact has a different "Contact ID" if that helps anything. I tried using a FILTER or INDEX MATCH but they didn't seem to pull any data, not sure if that's because there are multiple contacts.

Ideally, if I were to create a master sheet, I'd like just one instance of each registration and for each registration line, I would like the data for any contacts to the right, on the same line (so multiple cells from multiple rows). Then I could do a MailMerge Directory to make it easier to look at lol

Does anyone have any idea if or how this is possible? Let me know if you need clarification.


This is the registrations export:

SIRegRequestsExport (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE
1	CampaignID	RegistrationID	StudentSISID	StudentCurrentSchool	StudentCurrentSchoolName	StudentLastName	StudentFirstName	StudentPreferredFirstName	StudentDOB	StudentGender	StudentGenderDetails	StudentCurrentGrade	StudentStatusInCanada	StudentOEN	StudentAddressStreetNo	StudentAddressStreet	StudentAddressUnit	StudentCity	StudentPCode	StudentStateProv	StudentCountry	StudentPhone	StudentCell	StudentMedicalInfo	StudentMedicalInfoRemarks	StudentEpiPen	StudentMedicalInfoTwo	StudentMedicalInfoTwoRemarks	StudentEpiPenTwo	StudentMedicalInfoThree	StudentMedicalInfoThreeRemarks	StudentEpiPenThree	StudentMedicalInfoFour	StudentMedicalInfoFourRemarks	StudentEpiPenFour	StudentMedicalInfoFive	StudentMedicalInfoFiveRemarks	StudentEpiPenFive	StudentPhysicianName	StudentPhysicianNumber	StudentSpecialConditionOne	StudentSpecialConditionTwo	StudentSpecialConditionThree	RegistrationExtDaycare	RegistrationPlacementReq1	RegistrationPlacementReq2	RegistrationPlacementReq3	StudentPhysicallyFit	StudentRecorded	StudentCertifies	StudentCertifiesTwo	RegistrationWorkflowStatus	RegistrationCreateDate	RegistrationSubmissionDate	RegistrationLastStatusUpdateDate	RegistrationLastModifiedDate	OfficeNotes
2	364812426	364931462		138	Scool 1	Smith	Kylee		6/10/18	F		JK	Canadian Citizen	1234	25219	xx			123	Ontario	CA																						1	Both				1	1	TRUE	TRUE	Done	##########	##########	##########	##########
3	364812426	365189135		244	School 2	Jones	Kara		10/16/15	F		2	Canadian Citizen	1235	29	xx			456	Ontario	CA																						1	AM				1	1	TRUE	TRUE	Select Program	##########	##########	##########	##########
4	364812426	365288428			-1	Kind	Aiden		6/22/12	M		5	Canadian Citizen	1236	6	xx			789	Ontario	CA																						1	No				1	0	TRUE	TRUE	Select Program	##########	##########	##########	##########
5	364812426	365288446		379	School 3	Kind	Hudson		1/8/13	M		4	Canadian Citizen	1237	15	xx			12	Ontario	CA																						1	No				1	1	TRUE	TRUE	Select Program	##########	##########	##########	##########
RegRequestsExport

This is the contacts export:

SIRegRequestsContactsExport (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	CampaignID	RegistrationID	ContactID	ContactType	ContactLastName	ContactFirstName	ContactTelephoneNumber	ContactWorkPhone	ContactWorkPhoneExt	ContactMobilePhone	ContactEmailAddress	ContactRegistrantRelationship	ContactAuthorizePickup
2	364812426	364931462	364932127	1	Smith	Laura	905-123-4567				contact1@email.com	Parent
3	364812426	364931462	364932289	1	Smith	Eric	905-123-4568				contact2@email.com	Parent
4	364812426	365189135	365191392	1	Jones	Jerry	905-123-4569				contact3@email.com	Parent
5	364812426	365189135	365191087	1	Bass	Julia	905-123-4570				contact4@email.com	Parent
6	364812426	365288428	365288818	1	Kind	Claire	905-123-4571				contact5@email.com	Legal Guardian
7	364812426	365288446	365288830	1	Kind	Karen S	905-123-4572				contact6@email.com	Parent
8
RegRequestsContacts