Group and transpose part of the data table

Question

Hi everyone,

Since I'm a beginner, this is probably a very simple question - but searching for ideas to solve my problem didn't yield any useful results. I hope this post can help me find a solution from the community

I have this list of contacts (file attached) in which some of them look almost like duplicates. They share the same ID, Names, Address, Post code, but not in Component, and Sequence. I want to group all contacts that share similarities and then transpose other rows such as Facsimile, Phone, Email Address, and Contact into Columns.

Basically the data looks like this:

And I want to transform the whole dataset into something like this:

Expected Output

Any idea is very much appreciated!
Thanks

Contact list.xlsx

TonyA · Accepted Answer

I've attached a workflow that I think does what you want. It's a little more complex than I would like, but I couldn't think of a better way to handle the mix of different types of data in the "Email, Swift, BIC,Chips etc" column. Also, Crosstab sorts the rows and columns and I had to correct the final output for that by temporarily adding a row counter and a prefix to all the column names.

I did get a slightly different result from you for the company with ID 8222222. In the output you only had rows for that company with a Component value of FX, but the input file has values of FX and UK. It looks like you did want to retain both since the company with ID 6345789 has both UK and FX in the output and all other grouping columns are identical. Am I missing something?