Forum Discussion

MMcDonal's avatar
MMcDonal
Copper Contributor
Jul 02, 2019
Solved

Removing duplicates

  How do I remove duplicate values associated with a name only?  I have a number of the duplicate values that are repeated across a table range and correspond to different names (in the column to th...
BI & Data Analytics
Need Help
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 15, 2019

    MMcDonal 

    Thank you for the clarification. With this I'd use the formula as

    =IF( COUNTIF($D$2:$D2,$D3),0, SUMPRODUCT(('N:\Audit Database\2019 Audits\Los Angeles\November 2019\Environmental\Losses\[Environmental Loss Run 10.14.19.xlsx]Sheet1'!$A:$B=D3)*1))

    and apply to column custom number format as

    General;General;"-"??;@

    to hide zeros. 

MMcDonal's avatar
MMcDonal
Copper Contributor
Jul 08, 2019

Excel_World_Champion 

 

Thanks EWC.  I believe your method would remove all duplicate values irrespective of the duplicate values based on different sources.

 

See screenshot below of a larger spreadsheet where there are duplicate values.  My goal is to remove only duplicate values by source only.  So in this case, removal of all duplicate values by source (name), including the $3,000 duplicate value for GenD, but not for Henry S. and HewP since they are separate sources/names. 

 

The $3,000 values for GenD and HenryS should remain intact/not removed, since they are separate sources of the duplicate values within the range (spreadsheet).

 

 

Thanks!

 

MMcDonal

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 08, 2019

MMcDonal 

To remove by source select all columns in your range and check only one with source

Result is

  • MMcDonal's avatar
    MMcDonal
    Copper Contributor
    Jul 10, 2019

    SergeiBaklan 

     

    Thank you Sergei, particularly since it is a function I will need to use repeatedly on numerous similar projects going forward.  

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 10, 2019

      MMcDonal , Power Query could be a variant

      • MMcDonal's avatar
        MMcDonal
        Copper Contributor
        Oct 14, 2019

        SergeiBaklan

         

        Hi Sergei.  Coming back to you after 4 months. Hope all's well with you. 

         

        You were most helpful to me in July.  Was hoping you could you help me out with the following?

         

        I have a large spreadsheet with multiple duplicate values from the same client source. However, there are different policies assigned to the client source that I do not wish to remove when applying the  "remove duplicates" function.

         

        The attached sample shows duplicate values for different policies for the same client name. The source of the duplicate values (losses) is the client name and one or several of the policy (numbers) for that client name. For my needs, it does not matter which of the policies make up the duplicate value. 

         

        I would like to retain all policies relating to the same client source but reflect the duplicate value only once, as shown in the attached sample. 

         

        Thanks. 

         

         

Resources