Find duplication in two columns

[mahmoud]

Hello everyone! 

I have a table, as it is in this URL, 

https://www.dropbox.com/scl/fi/8qmd0ks7wb3rw5ac5pgoe/Example-v2.xlsx?dl=0&rlkey=z94e594t283zlwi03rse... 

I would to check:

    - if the Comp Name, CamPCode and ActCode are same then check the month if same month then the values are duplication at same month but if the months are different then duplication with other month or it can both cases togather.

   - if the CamPCode and ActCode are same then check the month if same month then the values are duplication at same month but if the months are different then duplication with other month or it can both cases togather.

   - if the Comp Name and ActCode are same then check the month if same month then the values are duplication at same month but if the months are different then duplication with other month or it can both cases togather.

Thanks for your support in advance!

Best Regards

Mahmoud

[v-yanjiang-msft]

Hi @mahmoud ,

According to your description, in my understanding, you want to display the part marked in red, here's my solution.

Create four calculated columns.

ConcatComp,Camp,Act =
IF (
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[ActCode] = EARLIER ( 'Table'[ActCode] )
                && 'Table'[CamPCode] = EARLIER ( 'Table'[CamPCode] )
                && 'Table'[Comp Name] = EARLIER ( 'Table'[Comp Name] )
        )
    ) > 1,
    CONCATENATE (
        CONCATENATE ( 'Table'[Comp Name], 'Table'[CamPCode] ),
        'Table'[ActCode]
    ),
    BLANK ()
)

ConcatComp,Camp =
IF (
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[CamPCode] = EARLIER ( 'Table'[CamPCode] )
                && 'Table'[Comp Name] = EARLIER ( 'Table'[Comp Name] )
        )
    ) > 1,
    CONCATENATE ( 'Table'[Comp Name], 'Table'[CamPCode] ),
    BLANK ()
)

ConcatCamp,Act =
IF (
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[ActCode] = EARLIER ( 'Table'[ActCode] )
                && 'Table'[CamPCode] = EARLIER ( 'Table'[CamPCode] )
        )
    ) > 1,
    CONCATENATE ( 'Table'[CamPCode], 'Table'[ActCode] ),
    BLANK ()
)

CheckIfSameOrgImplementedAtSameCamp =
VAR _C =
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[ConcatComp,Camp,Act] = EARLIER ( 'Table'[ConcatComp,Camp,Act] )
        )
    )
RETURN
    IF ( 'Table'[ConcatComp,Camp,Act] = BLANK (), 1, _C )

Get the final result.

I attach my sample for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[mahmoud]

Thanks @v-yanjiang-msft for your reply, 

It looks great! I noticed:

1. The table is showing the unique values, any suggestion to know which rows in the dataset are the duplicated?

2. I would to write somewhere in the dataset a note after I check the value if its not duplicated value or not and the value to be consider in next time I am checking the values as its not duplication. for example, it is showing the value C00011.1 as duplication, after review I fund it is not duplication at this time, so I would to wirte somewhere in the dataset a note it is not duplication and does not see it in coming months unlse a new value entred to the dataset has same value, then I see it again as a new expected duplication. 

Thanks for your support in advance!

Best Regards

Mahmoud

[v-yanjiang-msft]

Hi @mahmoud ,

Based on your sample, could you please show the finally expected result in an Excel? It will help to solve your problem faster.

Best Regards,
Community Support Team _ kalyj

[mahmoud]

Hi @v-yanjiang-msft 

Thanks for your follow and support again! 

Here is the new examplse: 

https://www.dropbox.com/scl/fi/8qmd0ks7wb3rw5ac5pgoe/Example-v2.xlsx?dl=0&rlkey=z94e594t283zlwi03rse...

I added an Index to know where is the duplication rows, 

And added a new value (green cells), so the duplication should not appear while there is note then appear again once I entered a new value.

I hope it is clear, thanks for your support and follow

Best Regards

Mahmoud