Jul 02 2019
07:20 AM
- last edited on
Jul 12 2019
11:26 AM
by
TechCommunityAP
Jul 02 2019
07:20 AM
- last edited on
Jul 12 2019
11:26 AM
by
TechCommunityAP
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 the right of the values.)
I only want to delete duplicate values associated with each corresponding name only, and not all duplicate values in the table range. Thanks.
Jul 03 2019 11:28 PM
Hey @MMcDonal
Try This:
* Select the range you want to remove the duplicates from, Go into the Data Tab, under the data tools group click Remove Duplicates:
* Check the two column you want to remove the Duplicates from and click OK
Jul 08 2019 07:48 AM
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
Jul 08 2019 11:21 AM
To remove by source select all columns in your range and check only one with source
Result is
Jul 10 2019 06:00 AM
Thank you Sergei, particularly since it is a function I will need to use repeatedly on numerous similar projects going forward.
Oct 14 2019 09:26 AM
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.
Oct 14 2019 03:11 PM
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.
Oct 15 2019 07:47 AM
Oct 15 2019 03:12 PM
SolutionThank 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.
Oct 16 2019 05:25 AM
Oct 15 2019 03:12 PM
SolutionThank 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.