How can I make my duplicate values null?

I was looking throughout the community thinking this was already asked, maybe it has been and I just couldnt find it, but I was curious to know if there was a way to make duplicate values show as null.

For example, lets say I have customer ID numbers as follows:

Customer ID #

12345

45832

78043

92378

72213

12345

I would like to have one of these customer ID numbers switched to null. Not the whole row of data just the one cell, or Customer ID #.

Thank you!

Preparation

Accepted answers

AndrewS

Hi @KonaCantTalk ,

I think the second workflow I attached yesterday might have gone unnoticed, it included a potential solution which used a unique tool to split, change the duplicates to Null and then Union back the dataset. I added in an additional duplicate value to test against more than 1 duplicate. Hope this helps.

All comments

AndrewS

Hi @KonaCantTalk

You could try a multiRow - I've attached a workflow which should help.

If you have more than two customer IDs you would need to amend the formula to:

if [Customer ID #] = [Row-1:Customer ID #] then null()
elseif isnull([Row-1:Customer ID #]) then null() else [Customer ID #] endif

EDIT - attached a second option using a unique tool. Too many choices!

Null.yxmd

Null2.yxmd

MarqueeCrew

I'd approach this by summarizing. First put a record Id on each record. Next summarize by grouping on your customer id and getting the first or minimum recordid. Now join this data back to your original data on customer id.

a formula could now be built to null ids

if recordid = min_recordid then custid

else null()

endif

cheers,

mark

KonaCantTalk

@MarqueeCrew,

Do you have a screenshot or could you be a little more descriptive with this? I think this is the direction I need to head in. I tried the other solution but I have multiple duplicates of the same record and the first solution seems geared towards if you only have one duplicate.

Also, for what it is worth, I already have a rather lengthy workflow going and have already spent some time cleaning my Customer ID column so if there is a way to do this without having to start from scratch that would be ideal.

I am new to this so thank you for your help!

MarqueeCrew

@KonaCantTalk ,

I've performed this with both a FIND REPLACE and a JOIN. The FIND REPLACE doesn't require a SORT.

Thanks for the opportunity to solve another post.

Cheers,

Mark

Null Duplicate Customer IDs.yxmd

Nezrin

What about filtering out duplicates from the unique tool and either use a formula tool to replace to null or use find and replace ?

KonaCantTalk

I will accept this as the solution since it works for the sample data set you provided. It didnt work for my data set though. I re-checked the steps and I mirrored what you did but it just changes my Customer ID column to 1, 2, 3, etc. to match. Not sure what I did wrong, I will try to look for other ways to do this.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703