Duplicate and random sample by unique ID

[brunosa]

Hi,

I've been facing a challenge to arrange some data I have. I have the table below. Due a technical limitation, I receive data like this where there is a duplicate for ID 1 (like sample 6033DDA) and I need to choose one of the ID 2 I see allocated to it, either ending HU1R005294 or HU4R003527 as example.

The issue is that right below for the next duplicate ID 1 (6043DDA) I will have to get either HU1R005294 in case I have considered already the HU4R003527 or the other way around.

I would either like to have the number HU4R003527 associated to both lines of 6033DDA, then HU1R005294 associated to both lines of 6043DDA. 

The count I put there because there are scenarios which I have 3 times the same ID 1 and 3 different ID 2, which I have to apply the same logic.

Hope I was clear enough. 

TIA

ID 1	ID 2	Count
2022080100000000006033DDA	HU1R005294	2
2022080100000000006033DDA	HU4R003527	2
2022080100000000006043DDA	HU1R005294	2
2022080100000000006043DDA	HU4R003527	2
2022080100000000006109DDA	282C001135	2
2022080100000000006109DDA	282C001138	2
2022080100000000006110DDA	282C001135	2
2022080100000000006110DDA	282C001138	2
2022080100000000006196DDA	362C000672	2
2022080100000000006196DDA	362C000674	2
2022080100000000006198DDA	362C000672	2
2022080100000000006198DDA	362C000674	2

[binuacs]

@brunosa Can you provide the expected result?

[AdwaitTarudkar]

Hi @brunosa 

Hope this helps-

1. Unique tool 1 - ID1

2. Unique tool 2 - ID 2

3. Join tool 1 - Join both U

4. Join tool 2 - Join both D

5. Union tool for the desired solution

[gyang3 · Accepted solution]

Hey @brunosa I've attached a workflow that'll get you to what I believe you're looking for your output. I've basically pulled in Unique tools to remove any duplicates and lastly join all of the unique records together. I've built this workflow taking into account scenarios in which you may have 3 of the same ID1. See screenshot below, and workflow attached.

If this helped solve your issue, please mark this as the solution. Thanks! :)

[brunosa]

Hi Adwait,

You did the join by specific fields? or position?

[brunosa]

Hi @gyang3, it worked perfectly for the 2 of same ID but when it brings down to 3, I have an issue still. Send how I would like it to be.

RAW DATA	RESULT WITH THE SOLUTION	DESIRED SOLUTION
ID 1	ID2	COUNT	ID 1	ID2	COUNT	ID 1	ID2	COUNT
2022080200000000007287DDA	C08C014804	3	2022080200000000007287DDA	C07C008677	3	2022080200000000007287DDA	C07C008677	3
2022080200000000007287DDA	C08C014786	3	2022080200000000007287DDA	C07C008677	3	2022080200000000007287DDA	C07C008677	3
2022080200000000007287DDA	C07C008677	3	2022080200000000007287DDA	C07C008677	3	2022080200000000007287DDA	C07C008677	3
2022080200000000007289DDA	C08C014804	3	2022080200000000007289DDA	C08C014786	3	2022080200000000007289DDA	C08C014786	3
2022080200000000007289DDA	C07C008677	3	2022080200000000007289DDA	C08C014786	3	2022080200000000007289DDA	C08C014786	3
2022080200000000007289DDA	C08C014786	3	2022080200000000007289DDA	C08C014786	3	2022080200000000007289DDA	C08C014786	3
2022080200000000007290DDA	C07C008677	3	2022080200000000007290DDA	C08C014786	3	2022080200000000007290DDA	C08C014804	3
2022080200000000007290DDA	C08C014786	3	2022080200000000007290DDA	C08C014786	3	2022080200000000007290DDA	C08C014804	3
2022080200000000007290DDA	C08C014804	3	2022080200000000007290DDA	C08C014786	3	2022080200000000007290DDA	C08C014804	3

[AdwaitTarudkar]

Hi @brunosa 

Join both on ID 1.

[gyang3 · Accepted solution]

@brunosa I've revised the workflow to get to get to your desired solution. I've included those with 2 counts as well in the workflow. However, it'll work whether you have 2 counts, 3 counts, or both in the same file. See attached. Let me know if that helps.