Transposition of values to a limited number of columns

[Marceli_Szadowiak]

Hello everyone,

I am trying to find an elegant solution to transform customer data table formatted in a single column as in the example below

Customer no.	Contact type	Value
123	Email	abc@email.com
123	Email	def@email.com
123	Email	ghi@email.com
123	Phone	111-111-111
123	Phone	222-222-222
123	Phone	777-777-777
456	Email	jkl@email.com
456	Email	mno@email.com
456	Phone	333-333-333
789	Email	prs@email.com
789	Email	tuv@email.com
789	Email	wxy@email.com
789	Email	zzz@email.com
789	Phone	444-444-444
789	Phone	555-555-555
789	Phone	666-666-666

to a new table where one customer record can hold up to two phone numbers and only one email address, as below

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

Thanks in advance for help

[Gaurav_Dhama_]

They both look the same. Can you confirm?

[Marceli_Szadowiak · Accepted solution]

Please check now

[Qiu · Accepted solution]

@Marceli_Szadowiak 
A bit twist on the Sequence Number arrangement. 😁

[Marceli_Szadowiak · Accepted solution]

Thank you, works brilliantly.

[Marceli_Szadowiak]

Hello again,

now I would like to find a solution for the initial contact 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 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 record 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.