Removing Duplicates Help

[waterbottle123]

Hi,

I have the following data set that needs to have duplicates removed:

Concatenate (Tracking ID + Lot Code)	Tracking ID	Lot Code	Formula to Indicate	Goal
XYZ-DOLPHIN-L92U12	XYZ-DOLPHIN	L92U12		Keep
XYZ-DOLPHIN-No Lot Code	XYZ-DOLPHIN	No Lot Code		Remove
XYZ-WALRUS-TNA2136	XYZ-WALRUS	TNA2136		Keep
XYZ-WALRUS-NN12345	XYZ-WALRUS	NN12345		Remove
XYZ-WALRUS-TT2136	XYZ-WALRUS	TT2136		Remove
XYZ-WALRUS-No Lot Code	XYZ-WALRUS	No Lot Code		Remove
XYZ-WALRUS-No Lot Code	XYZ-WALRUS	No Lot Code		Remove
XYZ-MONKEY-No Lot Code	XYZ-MONKEY	No Lot Code		Keep
XYZ-PARROT-No Lot Code	XYZ-PARROT	No Lot Code		Keep

<colgroup><col span="5"></colgroup><tbody>
</tbody>

I am unable to find a one step formula that would allow me to reach my intended result in the "Goal" section.

Notice how there are duplicate Tracking ID fields that is due to the value in the Lot Code column. For duplicate Tracking ID's that have unique Lot Codes, I want to keep the unique lot codes while removing the entry with "No Lot Code". However, if a Tracking ID does not have a Lot Code (as indicated by a single entry with "No Lot Code", I want to keep it.

Is this possible to do in one calculation?

I am able to do this with a two step process, where I add two extra columns to correspond to the table above.

First Step: =IF(A2=A1,"Duplicate","Keep") then I delete all Duplicates
Second Step: =IF(AND(D2=D1,I2="No Lot Code"),"Remove","Keep")

 

[mole999]

try > =COUNTIF($A$1:A1,A1) placed in Your Goal Column and tracked against your Tracking ID, and drag down, then delete anything greater than 1

 

[davesexcel]

You could use a countif()

=IF(COUNTIF($B$2:B2,B2)=1,"Keep","Remove")
Placed in E2 and drag down

 

[Amanda Wilso]

You can also remove duplicates by using :
Remove Duplicates tool, this allows you to find and remove absolute duplicates (cells or entire rows) as well as partially matching records (rows that have identical values in a specified column or columns). To perform this, follow the below steps.


Note. Because the Remove Duplicates tool permanently deletes identical records, it's a good idea to make a copy of the original data before removing duplicate rows.
To begin with, select the range in which you want to remove duplicates. To select the entire table, press Ctrl + A.
With the range selected, go to the Data tab > Data Tools group, and click the Remove Duplicates button.The Remove Duplicates dialog box will open, you select the columns to check for duplicates, and click OK.
To delete duplicate rows that have completely equal values in all columns, leave the check marks next to all columns, like in the screenshot below.
To remove partial duplicates based on one or more key columns, select only those relevant columns. If your table has many columns, the fastest way is to click the Unselect All button, and then select the columns you want to check for dupes.
If your table does not have headers, clear the My data has headers box in the upper-right corner of the dialog window, which is usually selected by default.
Done! All duplicate rows in the selected range are deleted, and a message is displayed indicating how many duplicate entries have been removed and how many unique values remain.


Another way to get rid of duplicates in Excel is separating unique values, and copying them to another sheet or a different workbook. The detailed steps follow below.


Select the range or the entire table that you want to deduce.
Navigate to the Data tab > Sort & Filter group, and click the Advanced button.
In the Advanced Filter dialog window, do the following:
Select the Copy to another location radio button.
Verify whether the correct range appears in the List Range This should be the range you've selected on step 1.
In the Copy to box, enter the range where you wish to copy the unique values (it's actually sufficient to select the upper-left cell of the destination range).
Select the Unique records only
Finally, click OK, and the unique values will be copied to a new location.

 

[waterbottle123]

You could use a countif()

=IF(COUNTIF($B$2:B2,B2)=1,"Keep","Remove")
Placed in E2 and drag down

This is similar to a formula that I tried as well however the issue comes at XYZ-WALRUS-TT2136 - which the formula tells it to "Remove It", when it needs to be kept since even though it is a duplicate tracking ID - it has a different lot code.

Is there something I am missing?

Thanks!

 

[waterbottle123]

bump