Delete Duplicate Rows

[geeklarokcmie]

Greetings All,

What tools can I use to output the below "results data" into an .xls doc based on the given data?

- Convert the "Names" row values into columns (removing duplicates)

- Remove duplicates of "Role", "Organization", "Employee ID" and connect them directly to the name. 

- Is it also possible to have age listed under the name?

"Given Data"

Names	Age	Role	Organization	Employee ID
John Doe	52	CSA	Amazon	FM985
John Doe	52	Lead CSA	Facebook	FM525
John Doe	52	CSM	Target	FM875
Jane Riley	34	CSA	Amazon	FM985
Jane Riley	34	Lead CSA	Facebook	FM525
Jane Riley	34	CSM	Target	FM875
Dristo	26	CSA	Amazon	FM985
Dristo	26	Server	Hilton	FM452
Dristo	26	Driver	Uber	FM111

Result Data :

Role	Organization	Employee ID	John Doe  (52)	Jane Riley (34)	Dristo (26)
CSA	Amazon	FM985	x	x	x
Lead CSA	Facebook	FM525	x	x	n/a
CSM	Target	FM875	x	x	n/a
Server	Hilton	FM452	n/a	n/a	x
Driver	Uber	FM111	n/a	n/a	x

Thank you!

[EmanueleE]

Hi ekocharla.

i think that you want to unpivot your data.

into the formula tool I create an header (name+age then unpivot it trough the cross tab) then dynamically rename the header at the last I create the x and n/a value for the records.

in attachment a flow v2020.2

 

[grazitti_sapna · Accepted solution]

Hi @geeklarokcmie , please refer to the workflow and screenshot for the desired output.

[geeklarokcmie]

Hi Emanuele,

I appreciate your response and sharing the workflow. 

However, when I tried this in the actual data that i wanted to transform to -  it kept throwing an error in the formula tool as below :

Error: Formula (2): Parse Error at char(0): Unknown variable "Header" (Expression #1)

Please note that the solution from Sapna worked.

Thank you for your efforts. 

[geeklarokcmie]

Hi Sapna,

This solution worked like a charm. 

Thank you so much for providing the accurate tools.

I love Alteryx - made working with excel so much easier.