delete pairs of duplicates from multiple rows and create a unique pair

[ChandraS]

I want to delete pairs of duplicates from multiple rows and create a unique pair.

My data is looks like below:

RecordID	Unique_record	Comp1	Comp1_win	Comp1_price	Comp2	Comp2_win	Comp2_price	Comp3	Comp3_win	Comp3_price
1	ADL402019-07-30	Abc	Yes	123	xyz	No	456
2	ADL402019-07-30	pqr	Yes	789
3	ADL402019-07-30	xyz	No	456
4	ADL402019-07-30	Abc	Yes	123	xyz	No	456	lmn	Yes	101112

I want the output to be:

Unique_record

Comp1

Comp1_win

Comp1_price

Comp2

Comp2_win

Comp2_price

Comp3

Comp3_win

Comp3_price

Comp4

Comp4_win

Comp4_price

ADL402019-07-30

Abc

Yes

123

xyz

No

456

pqr

Yes

789

lmn

Yes

101112

[grazitti_sapna]

Hi @ChandraS , try this workflow, I hope it helps. Also please refer to the screenshot with the desired output and mark this post as a solution if works!

[DavidP · Accepted solution]

Here's my attempt

[RolandSchubert]

Hi @ChandraS ,

is a bit more complicated than it seemed ... had to convert it first to a "general" list with columns comp, price and win and re-create to columns. I've attached a sample workflow.

Let me know if it works for you.

Best,

Roland

[ChandraS]

Hi DavidP,

Thank you for the quick response. None of the solutions are working for me.

Actually, i have 30 odd other fields(including RecordID), RecordID is used in deriving couple of fields, so,  i don't want to recreate a new RecordID. I have around 20 competitors(each with name, win, price combination), i have to make this unique pairs per Unique record and then map the remaining fields based on Unique record. Hope this makes the requirement clear. Thank you

[danilang]

Hi @ChandraS 

Can you provide some sample data, with all the fields along with a list of which fields are dynamic.  We need this because the various transformations performed on the data mean that the output is dependent on the fields contained in the input set.  The summarization and crosstab tools may be removing some of the fields that you need.  Without a complete list of input fields, no solution will completely solve your problem.   

If you can't provide a complete sample input, the best that we can do is provide solutions that work with the data that you have provided.  Any additional fields may require modification to these solutions to produce the output you're looking for.  If you study these solutions you may be able to learn enough about the techniques used to adapt them to handle your new requirements.  If you have specific questions about how any of the solutions work, post them here and we'll gladly help you.

Dan