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

[Andrew van den Berg]

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 -->

 

[Marcelo Branco]

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?

 

[hiker95]

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
	M	N
1	47-4068	1
2	47-4677	1
3	47-4084	1
4	47-4084	2
5	47-4085	1
6	47-4085	2
7	47-4084	1
8
Sheet1

After the macro:

Excel Workbook
	M	N
1	47-4068	1
2	47-4677	1
3	47-4084	2
4	47-4085	1
5	47-4085	2
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.

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.

 

[hiker95]

Andrew van den Berg,

What version of Excel are you using?

What is the worksheet name where your raw data is located?

 

[Andrew van den Berg]

Excel 2003
File name is "2011 feb Sprint AvdB"

 

[hiker95]

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?

 

[Andrew van den Berg]

worksheet name "1 and 8 feb" (i assume you mean the name of the tab)

data begins in row 2 (row 1 is the headers-row).



thanks

 

[Marcelo Branco]

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.

 

[hiker95]

Andrew van den Berg,


Sample raw data befor the macro:

Excel Workbook
	M	N
1	Title M	Title N
2	47-4068	1
3	47-4677	1
4	47-4084	1
5	47-4084	2
6	47-4085	1
7	47-4085	2
8	47-4084	1
9
1 and 8 feb

After the macro:

Excel Workbook
	M	N
1	Title M	Title N
2	47-4068	1
3	47-4677	1
4	47-4084	2
5	47-4085	1
6	47-4085	2
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.

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.

 

[hiker95]

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?