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

[Marceli_Szadowiak]

Hello @Deano478 , @jrlindem 

let me explain what I mean and where I've got so far. In the attached workflow I have solutions for rules no. 2 and 3.

The Contact record formatting container transforms the input so that each row for a particular account is filled with one email and up to 2 phone numbers and produces the following output:

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

The Relationship container holds the solution for indicating which account is primary for each contact and including the non-primary accounts in consecutive Relationship columns (accounts without shared contacts intentionally excluded):

Customer no.	Contact type	Value	Primary account for multiple-account contacts	Relationship1	Relationship2	Relationship3
123	Phone	111-111-111	YES	666
123	Email	abc@email.com	YES	222	333	444
123	Phone	777-777-777	YES	222	555
789	Phone	444-444-444	YES	999
789	Phone	555-555-555	YES	999
789	Email	wxy@email.com	YES	999
789	Email	prs@email.com	YES	999
789	Email	tuv@email.com	YES	999
789	Phone	666-666-666	YES	999
789	Email	zzz@email.com	YES	999

The next step I need to make is to marry these two result and include rule 1, i.e. no duplicate emails or phone numbers, while retaining rule 2 and 3. In other words, the final output must look like this:

Customer no.	Email	Phone1	Phone2	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	mno@email.com	333-333-333
789	prs@email.com	444-444-444	555-555-555	999
789	tuv@email.com	666-666-666		999
789	wxy@email.com			999
789	zzz@email.com			999

Please let me know if anything is unclear, thanks