Transposition of unique values to a limited number of columns

Question

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 typeValuePrimary account for multiple-account contacts123Emailabc@email.comYES123Emaildef@email.comN/A123Emailghi@email.comN/A123Phone111-111-111YES123Phone222-222-222N/A123Phone777-777-777YES222Emailabc@email.comNO222Phone777-777-777NO222Phone777-777-777NO333Emailabc@email.comNO444Emailabc@email.comNO555Phone777-777-777NO666Phone111-111-111NO456Emailjkl@email.comN/A456Emailmno@email.comN/A456Phone333-333-333N/A789Emailprs@email.comYES789Emailtuv@email.comYES789Emailwxy@email.comYES789Emailzzz@email.comYES789Phone444-444-444YES789Phone555-555-555YES789Phone666-666-666YES999Emailprs@email.comNO999Emailtuv@email.comNO999Emailwxy@email.comNO999Emailzzz@email.comNO999Phone444-444-444NO999Phone555-555-555NO999Phone666-666-666NO

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.Phone1Phone2EmailRelationship1Relationship2Relationship3123  abc@email.com222333444123  def@email.com   123  ghi@email.com   123111-111-111  666  123222-222-222     123777-777-777  222555 456  jkl@email.com   456333-333-333 mno@email.com   789444-444-444555-555-555prs@email.com999  789 666-666-666tuv@email.com999  789  wxy@email.com999  789  zzz@email.com999

Looking forward to hearing your feedback, thanks.

Marceli_Szadowiak · Answer

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 contactsEmailPhone1Phone2123N/Aghi@email.com  123N/Adef@email.com222-222-222 123YESabc@email.com111-111-111777-777-777222NOabc@email.com777-777-777 333NOabc@email.com  444NOabc@email.com  456N/Amno@email.com  456N/Ajkl@email.com333-333-333 555NO 777-777-777 666NO 111-111-111 789YEStuv@email.com666-666-666 789YESzzz@email.com  789YESwxy@email.com  789YESprs@email.com444-444-444555-555-555999NOwxy@email.com  999NOprs@email.com444-444-444555-555-555999NOtuv@email.com666-666-666 999NOzzz@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 typeValuePrimary account for multiple-account contactsRelationship1Relationship2Relationship3123Phone111-111-111YES666  123Emailabc@email.comYES222333444123Phone777-777-777YES222555 789Phone444-444-444YES999  789Phone555-555-555YES999  789Emailwxy@email.comYES999  789Emailprs@email.comYES999  789Emailtuv@email.comYES999  789Phone666-666-666YES999  789Emailzzz@email.comYES999

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.EmailPhone1Phone2Relationship1Relationship2Relationship3123abc@email.com  222333444123def@email.com     123ghi@email.com     123 111-111-111 666  123 222-222-222    123 777-777-777 222555 456jkl@email.com     456mno@email.com333-333-333    789prs@email.com444-444-444555-555-555999  789tuv@email.com666-666-666 999  789wxy@email.com  999  789zzz@email.com  999

Please let me know if anything is unclear, thanks

Transposition of unique values.yxmd

jrlindem · Answer

@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

Deano478 · Answer

@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?