Removing Duplicates with custom conditions

I have a requirement to delete duplicates with some custom conditions. Consider the Element "occurrence" in below image for your reference, I need a formula to automate this boolean element. Conditions are as follows

1.First two elements with first data "11" has Second Data "0" and "1" , In such a case , we need to check the one with "0".

2. Next three elements have similar conditions , hence we need to check both elements with value "0".

3. Next three elements with "13" has all "0" s in Second Data , hence any two can be checked.

4. All the other elements have unique values for "First Data" and hence all these needs to be unchecked..

Assumptions

There is at most one element with a particular value of "First Data' having "second Data" as "1".

Any help will be appreciated

Thanks and Regards

Aakash Sachdeva

Accepted answers

VIGNESH.M

@aakash ,

Try this, it works without import, just formula only.

Regards,

Vignesh M

All comments

VIGNESH.M

Hi Aakash,

Try this,

Step 1: Create a list called "First Data" and populate this list from column 1(First Data) of Module.\

Create a import action and use it.

Step 2: Create below line items and use the formula given below

Let me know if i understood incorrectly.

Regards,

Vignesh M.

Misbah

@VIGNESH.M

Let me share my thoughts here.

It might not work when there are more than 3 entries in first data column when the expectation is to take only first two into consideration - your approach will take n-1 entries into consideration for n entries.

Please correct me if I got it wrong

Thanks,

Miz

VIGNESH.M

@Misbah, the solution will work for more than 3 entries. My understanding is that, there shouldn't be any duplicate following custom condition and it's not like we should consider first 2 entries only. Please check the screen shot provided in the solution.

Misbah

@VIGNESH.M

I think I was not able to put down my thoughts effectively, hence sharing the screenshot

Mimicked your solution here.

If you change Old East's (Row 2) value from 11 to 12, see what happens to the Boolean checks - it checks 3 boxes of First data 12 when it should have stopped at 2 of the boxes (Which was the expectation)

Thanks,
Misbah

Hope

VIGNESH.M

@Misbah , here the request is remove duplicates, it's not like we have to take only first 2 entries data.

N-1 records should be deleted from N records, this is the expectation.

@aakash , Can you confirm whether the solution work or not.

~Vignesh M

aakash

Hi @Misbah , @VIGNESH.M

I appreciate your efforts and Vignesh your approach will work, but In this case I am looking for just a formula based solution, I can implement this with import easily as you suggested . I would appreciate if anyone can provide any formula based solution.

Thanks for your help

Aakash Sachdeva

VIGNESH.M

@aakash ,

Try this, it works without import, just formula only.

Regards,

Vignesh M

Misbah

@VIGNESH.M

What threw me off was Aakash mentioning the condition that any two can be checked (Condition number 3).

Anyway if Aakash believes that it exactly fits his requirement then case closed. Awesome & Good Job.

Thanks,

Misbah

rob_marshall

@aakash ,

If I am understanding this correctly, you can concatenate the fields you need and encapsulate them within the IsFirstOccurrence() formula as the first parameter. This should get you the same result without using the Rank function.

Hope this helps,

Rob

aakash

Hi all

@VIGNESH.M

Thank you so much for the efforts , both your solutions work perfectly.

@Misbah

Thanks , the case you pointed out is working perfectly as I required , the requirement is to remove N-1 entries always.

@rob_marshall

Thanks , ISFIRSTOCCURRENCE was the first thing that I thought about while implementing this, I have already tried concatenating the two columns and using ISFIRSTOCCURRENCE but it fails for a specific case, e.g I have first data as 14 and second data as 0 , 14 is not repeated anywhere in the First Data, hence 14-0 should be unchecked. Next consider the combination as 15-0 and 15-1 , in this case both are occurring only one time, but the one with "0" should be checked. Hence I could not use that Function.

Let me re explain the requirements in a generic manner that will help you understand my case more clearly.

1. I need to remove duplicates so that one value should occur only once in "First Data" column.

2. In case of repetition in first data column, consider second data, if any of them contains a non zero value(for same value of FirstData) , that one should stay and other one should be deleted. (only one of them will contain a non zero value at a time)

3. If in second data , all contains only "0" s , then I need to keep one of them and remove others.

All the requirements have been satisfied with @VIGNESH.M 's solution.

Thanks and Regards

Aakash Sachdeva

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…