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. 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 14, 2019

MMcDonal 

In your latest sample it's not removing of duplicates for which you remove entire rows based on some criteria. Here you'd like to empty repeating values in first and second columns keeping the rest untouched.

Better to do that with formulas in these first columns. Logic of formula depends on logic of the data structuring - will it be sorted/filtered or not; is it necessary to treat differently zero values and empty cells; shall we empty these values only for sequentially repeating values in 4th column or that doesn't matter. For example, in your sample you empty values for GGG Demolition, Inc. but not for Bairos Recycling Inc, which also is repeating with non-zero values, but not one by one.

 

For example, in A3 it could be

=IF( (D3=D2)*(A2>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))

, similar in B3 and we may drag them down.

 

Perhaps above formulas shall be more complex, but again, it depends logic of data itself.

MMcDonal's avatar
MMcDonal
Copper Contributor
Oct 15, 2019
Thanks Sergei. I will take a look at your formula and see if it works for my purpose.
You raised some good questions: (a) The data does not have to be filtered/sorted; (b) there is no difference between cells with zero values and empty cells. (I removed the repeated (non-zero) values for GGG Demolition under to "To This" data set and left those cells blank/empty, and (c) I overlooked the second/lower $810 entry for Bairos Recycling, which I should have cleared/deleted under the "To This" data group, same as I did with GGG Demolition.
Again, thanks.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 16, 2019

    MMcDonal 

    Michael, you are welcome

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

    SergeiBaklan 

     

    This is [very] good.   Thanks Sergei for your time, effort, and expertise.

     

    Michael

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    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. 

Resources