Duplicate rows based on one column - how to delete, based on value of another column???

A

Andrew van den Berg

New Member
Joined
Jul 3, 2011
Messages
11
The following situation in a file with tens of thousand of rows:
Column M Column N
47-4068 1
47-4677 1
47-4084 1
47-4084 2
47-4085 1
47-4085 2

What to do if i want to delete the bold rows (duplicate values in column M and cell value in column N is 1)???
Do i need a Macro for this? pls explain how i need to proceed then (i am a basic user).
Thanks!!!!
<!-- / message -->
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Hi Andrew,

I think you'll need a macro.

But there are risks running a macro to delete rows. So, please, tell me:
What is the name of the sheet that contains the data?
In which row the data begin in columns M and N?
Column X is free?
And, very important, do you have copies of your workbook? (Just in case...)

M.
ps: do you have any experience with macros? Creating and/or running?
 
Upvote 0
Andrew van den Berg,


I assume that your raw data in columns M and N begin in cells M1 and N1 respectively. If this is not correct, then what row does the raw data begin in?


Sample raw data before the macro (the cells that are BOLD are duplicates):


Excel Workbook
MN
147-40681
247-46771
347-40841
447-40842
547-40851
647-40852
747-40841
8
Sheet1





After the macro:


Excel Workbook
MN
147-40681
247-46771
347-40842
447-40851
547-40852
6
7
8
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub DelDupeRows()
' hiker95, 07/03/2011
' http://www.mrexcel.com/forum/showthread.php?t=561746
Dim LR As Long, LC As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, "M").End(xlUp).Row
LC = Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
Cells(1, LC + 2).Formula = "=M1&N1"
Cells(1, LC + 2).AutoFill Destination:=Range(Cells(1, LC + 2), Cells(LR, LC + 2))
With Range(Cells(1, LC + 2), Cells(LR, LC + 2))
  .Value = .Value
End With
With Range(Cells(1, LC + 3), Cells(LR, LC + 3))
  .FormulaR1C1 = "=COUNTIF(R1C" & LC + 2 & ":R" & LR & "C" & LC + 2 & ",RC[-1])"
  .Value = .Value
End With
With Range(Cells(1, LC + 4), Cells(LR, LC + 4))
  .FormulaR1C1 = "=IF(RC[-1]>1,"""",RC[-1])"
  .Value = .Value
End With
On Error Resume Next
Range(Cells(1, LC + 4), Cells(LR, LC + 4)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Range(Cells(1, LC + 2), Cells(LR, LC + 4)).Clear
Application.ScreenUpdating = True
End Sub


Then run the DelDupeRows macro.
 
Upvote 0
Andrew van den Berg,

What version of Excel are you using?

What is the worksheet name where your raw data is located?
 
Upvote 0
Andrew van den Berg,

What is the worksheet name where the raw data is?

What row does the raw data in columns M and N beging in?
 
Upvote 0
Hiker,

I assumed, accordingly with OP, that the criterium is:
Delete if a value is duplicate in column M and, after that is found, if column N = 1

What is different from duplicates only being considered as
Column M AND Column N identicals.

(hope i made myself clear)

Not sure who is right....

M.
 
Upvote 0
Andrew van den Berg,


Sample raw data befor the macro:


Excel Workbook
MN
1Title MTitle N
247-40681
347-46771
447-40841
547-40842
647-40851
747-40852
847-40841
9
1 and 8 feb





After the macro:


Excel Workbook
MN
1Title MTitle N
247-40681
347-46771
447-40842
547-40851
647-40852
7
8
9
1 and 8 feb





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub DelDupeRowsV2()
' hiker95, 07/03/2011
' http://www.mrexcel.com/forum/showthread.php?t=561746
Dim LR As Long, LC As Long
Application.ScreenUpdating = False
Worksheets("1 and 8 feb").Activate
LR = Cells(Rows.Count, "M").End(xlUp).Row
LC = Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
Cells(2, LC + 2).Formula = "=M2&N2"
Cells(2, LC + 2).AutoFill Destination:=Range(Cells(2, LC + 2), Cells(LR, LC + 2))
With Range(Cells(2, LC + 2), Cells(LR, LC + 2))
  .Value = .Value
End With
With Range(Cells(2, LC + 3), Cells(LR, LC + 3))
  .FormulaR1C1 = "=COUNTIF(R1C" & LC + 2 & ":R" & LR & "C" & LC + 2 & ",RC[-1])"
  .Value = .Value
End With
With Range(Cells(2, LC + 4), Cells(LR, LC + 4))
  .FormulaR1C1 = "=IF(RC[-1]>1,"""",RC[-1])"
  .Value = .Value
End With
On Error Resume Next
Range(Cells(2, LC + 4), Cells(LR, LC + 4)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Range(Cells(2, LC + 2), Cells(LR, LC + 4)).Clear
Application.ScreenUpdating = True
End Sub


Then run the DelDupeRowsV2 macro.
 
Upvote 0
Andrew van den Berg,


If the worksheet/tab name will change, then let me know, and I will adjust the macro to be generic, with some testing for the titles in M1 and N1.

If this is the case, what are the titles in cell M1 and N1?
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
How can I prevent duplicate record by validating two columns in my worksheet
Replies
1
Views
199
Engberg
E
S
  • Question Question
SUMIFS while ignoring duplicate rows based on values from a column
Replies
4
Views
345
slam
S
J
  • Question Question
Copying Rows Based on Column Value
Replies
1
Views
151
jolivanes
jolivanes
T
  • Question Question
Remove Duplicates with Dynamic Columns Array
Replies
0
Views
668
trough
T
J
  • Question Question
Change specific values (not all) in a VBA sub
2
Replies
16
Views
784
juscuz419
J

Forum statistics

Threads
1,224,557
Messages
6,179,508
Members
452,918
Latest member
Davion615

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