Duplicate data in multiple columns - how to resolve

Hi All - I'm hoping someone can help me solve my issue. I want to identify if only 6 columns in my massive data set have all the same values in. I have attached a before and after so can hopefully understand what I want to return. After I identify which lines are duplicate, I then need to get rid of the line or multiple lines that are duplicates from my data but obviously keeping the lines that are not duplicate. What tool can I use to get rid of these as if I just filtered by 'yes' would still bring back the duplicate lines. Any help would be much appreciated

Dupliacte.xlsx

Workflow

Help

Accepted answers

Raj

@binsell
one way of doing this
find workflow attached

mark done if solved.

1201.yxmd

binu_acs

@binsell one way of doing this with the batch macro

Duplicate_05062024.yxzp

All comments

caltang

You can use the Multi Row tool to check. Configure it to create a new field and use an IF statement. In this case, you can use IF Column = Column + 1 AND Column_2 = Column_2 + 1 AND … THEN “Duplicate” ELSE “Not duplicate” ENDIF. The AND … indicates the columns you want to put as the check.

Make sure to group by a unique code that ties the rows together.

caltang

Alternatively, if it’s just two rows each group, you could pivot it and then transpose based on a Mod count (1,2) of the groups. Then you can either use a select tool to split them and join them again OR you could use a formula tool to directly compare + a summarise tool to group and concat the status (Duplicate/Not duplicate). So if Not duplicate exists, then that row is not a duplicate, but if only duplicate exists, then you have your status.

caltang

In addition, may be different to your request, you can also use the cross tab and transpose tools to see how many duplicates are there in the columns - either a full duplication or just partial. Not sure if that’s useful but something worth considering and trying. I hope my comments help! Currently on my phone so I cannot build something for you.

binsell

Thanks for taking the time to reply, as I'm really new to Alteryx, I haven't used all the tools you are talking about and wonder if you wouldn't mind putting in a work flow so I can see and use the formula? Thank you in advance!

binsell

Just seen your comment about being on your phone! I will try my best to build but think I will get a bit stuck as not used some of the tools before sadly. Thank you again

Raj

@binsell
one way of doing this
find workflow attached

mark done if solved.

1201.yxmd

binu_acs

@binsell one way of doing this with the batch macro

Duplicate_05062024.yxzp

binsell

Thank you - wasn't actually as hard as I thought! Thank you again

binsell

Amazing as always! I think you have replied to everyone single one of my posts! You are the master! Thank you

binsell

@Raj Thank you for this, after we have identified which lines are duplicates, how do I then get rid of the duplicate line and just keep one line in my data. The formula provides me to identify which ones are duplicates, but I then want to filter them out and just keep one of the lines, if that makes sense! I was thinking about a unique tool but it didn't seem to work and lost some data. Thank you so much

binsell

Thank you @binu_acs as always! So once I have identified which is the duplicate line, how do I get rid of the duplicate line only so keeping one line of the data? Thank you

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335