Transposition of unique values to a limited number of columns

[Marceli_Szadowiak]

Hello All,

I have a question that is closely related to one posted and solved here 

Now I would like to find a solution for an initial contact data input which looks like this:

Customer no.	Contact type	Value	Primary account for multiple-account contacts
123	Email	abc@email.com	YES
123	Email	def@email.com	N/A
123	Email	ghi@email.com	N/A
123	Phone	111-111-111	YES
123	Phone	222-222-222	N/A
123	Phone	777-777-777	YES
222	Email	abc@email.com	NO
222	Phone	777-777-777	NO
222	Phone	777-777-777	NO
333	Email	abc@email.com	NO
444	Email	abc@email.com	NO
555	Phone	777-777-777	NO
666	Phone	111-111-111	NO
456	Email	jkl@email.com	N/A
456	Email	mno@email.com	N/A
456	Phone	333-333-333	N/A
789	Email	prs@email.com	YES
789	Email	tuv@email.com	YES
789	Email	wxy@email.com	YES
789	Email	zzz@email.com	YES
789	Phone	444-444-444	YES
789	Phone	555-555-555	YES
789	Phone	666-666-666	YES
999	Email	prs@email.com	NO
999	Email	tuv@email.com	NO
999	Email	wxy@email.com	NO
999	Email	zzz@email.com	NO
999	Phone	444-444-444	NO
999	Phone	555-555-555	NO
999	Phone	666-666-666	NO

The output has to be formatted in accordance with the following rules:

1. there can be no duplicate contacts in the output file

2. if a contact (either an email or a phone number) is shared by more than one account, the account indicated as the Primary account for that contact is to be retained in the Customer no. column and all the remaining accounts sharing that contact need to be indicated in consecutive Relationship columns.

3. each row can hold 1 email and up to 2 phone numbers. 

Based on the rules stated above, the output file should look like this:

Customer no.	Phone1	Phone2	Email	Relationship1	Relationship2	Relationship3
123			abc@email.com	222	333	444
123			def@email.com
123			ghi@email.com
123	111-111-111			666
123	222-222-222
123	777-777-777			222	555
456			jkl@email.com
456	333-333-333		mno@email.com
789	444-444-444	555-555-555	prs@email.com	999
789		666-666-666	tuv@email.com	999
789			wxy@email.com	999
789			zzz@email.com	999

Looking forward to hearing your feedback, thanks.

[Deano478]

@Marceli_Szadowiak Can you please explain what you have tried to do in order to solve this problem so far that way we can go from there?

[jrlindem]

@Marceli_Szadowiak it also appears there's some logic missing.  Take Customer No. 789, for example, how would you know when the 666-666-6666 should drop down to the second entry?  It appears that since there is a third phone number it's being appended onto the second email address...

Ultimately, after having played around with this for a while, it appears there's some missing information that would be necessary to join up or merge all the data as you've arranged it in your example output.  Can you please clarify or attach what you've done so far as @Deano478 has suggested?

Cheers,  -Jay