Need to remove duplicates but replace them with a value

F

forginganewone

Board Regular
Joined
Mar 14, 2018
Messages
64
[FONT=&quot]I want to remove duplicate serial numbers from column A but the 1 serial that will be kept should have network category from column of "NW*"[/FONT]
[FONT=&quot]Secondly I want to keep track of which serials will be removed so i will add another column and replace the removed serials with "To be removed" and the 1 serial that is kept saying "To be kept" (In the separate column that will be created)[/FONT]
[TABLE="class: s570a4-19 MzlEq"]
<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TH="class: s570a4-25 hWWGpP, align: center"]Serial Number[/TH]
[TH="class: s570a4-25 hWWGpP, align: center"]Site id[/TH]
[TH="class: s570a4-25 hWWGpP, align: center"]Network Category[/TH]
[/TR]
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"]AA22[/TD]
[TD="class: s570a4-21 eUFXof"]1123[/TD]
[TD="class: s570a4-21 eUFXof"]NW-Antenna[/TD]
[/TR]
[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"]AA22[/TD]
[TD="class: s570a4-21 eUFXof"]3221[/TD]
[TD="class: s570a4-21 eUFXof"]NW-IDU[/TD]
[/TR]
[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"]AA22[/TD]
[TD="class: s570a4-21 eUFXof"]2299[/TD]
[TD="class: s570a4-21 eUFXof"]NW-ODU[/TD]
[/TR]
[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"]AA22[/TD]
[TD="class: s570a4-21 eUFXof"]2991[/TD]
[TD="class: s570a4-21 eUFXof"]NS-sz[/TD]
[/TR]
[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"]AA22[/TD]
[TD="class: s570a4-21 eUFXof"]7521[/TD]
[TD="class: s570a4-21 eUFXof"]NM-sd[/TD]
[/TR]
[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"][/TD]
[TD="class: s570a4-21 eUFXof"][/TD]
[TD="class: s570a4-21 eUFXof"][/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]What i had in mind was simply to use remove duplicate option on Column A:A and then use create new column and use formula =if($A1="","To be removed","To be kept")[/FONT]
[FONT=&quot]But the problem with the above solution would be that i want the remaining serial from category "NW*" in column C[/FONT]
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
try this formula in D2 copied down

=IF(COUNTIF(A$2:A2,A2)=1,A2,"remove")

(assumes first data row is row 2)
 
Last edited:
Upvote 0
Yongle said:
All of them or the first one?
Click to expand...

If same serial has a "NW*" category and any other category in column C, it should keep the row value of "NW*" and remove other duplicates.

But if there are no "NW*" category in column c and just random categories, it should keep any 1 of the row value and remove other duplicates.
 
Upvote 0
Yongle said:
All of them or the first one?
Click to expand...

[TABLE="class: cms_table_s570a4-19 cms_table_MzlEq"]
<tbody>[TR="class: cms_table_s570a4-20 cms_table_bKSoXW"]
[TH="class: cms_table_s570a4-25 cms_table_hWWGpP, align: center"]Serial Number[/TH]
[TH="class: cms_table_s570a4-25 cms_table_hWWGpP, align: center"]Site id[/TH]
[TH="class: cms_table_s570a4-25 cms_table_hWWGpP, align: center"]Network Category[/TH]
[TH="class: cms_table_s570a4-25 cms_table_hWWGpP, align: center"]Desired result[/TH]
[/TR]
[TR="class: cms_table_s570a4-20 cms_table_bKSoXW"]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]AA22[/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]1123[/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]NS-Antenna[/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]removed[/TD]
[/TR]
[TR="class: cms_table_s570a4-20 cms_table_bKSoXW"]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]AA22[/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]3221[/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]NW-IDU[/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]AA22[/TD]
[/TR]
[TR="class: cms_table_s570a4-20 cms_table_bKSoXW"]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]AA22[/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]2299[/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]NW-ODU[/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]removed[/TD]
[/TR]
[TR="class: cms_table_s570a4-20 cms_table_bKSoXW"]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]AA22[/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]2991[/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]NS-sz[/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]removed[/TD]
[/TR]
[TR="class: cms_table_s570a4-20 cms_table_bKSoXW"]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]AA22[/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]7521[/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]NM-sd[/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"]removed[/TD]
[/TR]
[TR="class: cms_table_s570a4-20 cms_table_bKSoXW"]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"][/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"][/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"][/TD]
[TD="class: cms_table_s570a4-21 cms_table_eUFXof"][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
What happens if there is also

AA23 with network category NW-IDU

Is that retained or deleted?
 
Upvote 0
Yongle said:
What happens if there is also

AA23 with network category NW-IDU

Is that retained or deleted?
Click to expand...

Good question. So i am deleting duplicate serials from a list of 50,000 rows, meaning there are 4382 unique serials and 45618 duplicates.

For each Serial i want it to retain the unique value against the "NW*" if there is no "NW*" category then it should retain any 1 and if there are "NW*" plus other categories in column C then it should prioritize "NW*"

Priority :
1) NW* or ANY = NW*
2) Only ANY = Any 1
3) NW and NW = Any 1 NW
 
Last edited:
Upvote 0
Try this

=IF(LEFT(C2,2)="NW",IF(COUNTIF(A$2:A2,A2)=1,A2,"Remove"),IF(COUNTIF(A$2:A2,A2)=1,A2,"Remove"))
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
VBA macro to create unique permutations from different columns
2
Replies
12
Views
2K
Nine Zero
N
M
  • Question Question
Removing duplicates from each column
Replies
3
Views
1K
Fluff
Fluff
Raaj Chauhan
  • Question Question
Macro to prevent duplicate entries in multiple columns
Replies
1
Views
2K
Fluff
Fluff
S
  • Question Question
Excel VLOOKUP replace found value to jump on next value
Replies
0
Views
418
stefutz
S
N
  • Question Question
VBA - Remove rows from XLSX where particular text can be found within a cell
Replies
5
Views
760
novadon
N

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top