# Removing duplicate entries?

#### hrithik

##### Active Member
I have the following data in an excel sheet
A B C D E F G
unique member_id name phone email-id address date

I have the formula "IF(B2=B3,0,1)" in cell A2 and copied down column A.

I have duplicate entries in B, which sets the corresponding "unique" cell as '0'. Suppose cell A5 has unique field as '0';
Step 1: search for cells (like A5) in col A
Step 2: Copy all data in A6:G(last row) and paste it on A5:G5 row, thus removing the duplicate entry in A5:G5.

I have written the following macro to search for cells in col. A with value as '0'; I'm having a hard time writing the copy and paste macro to perform step 2. Please help!

Macro:
Sub NextZero()
Dim LR As Long, i As Long, b As Boolean, count As Long
count = 0
LR = Range("A" & Rows.count).End(xlUp).Row
If ActiveCell.Column <> 1 Then Range("A1").Select
For i = ActiveCell.Row + 1 To LR + 1
With Range("A" & i)
If .Value <> "" And Not .Value = "1" Then
b = True
Application.Run "CopyPaste"
count = count + 1
.Select
Exit For
End If
End With
Next i
If Not b Then MsgBox "No more", vbInformation

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### venkat1926

##### Well-known Member
Do you need a macro.It would have been convenient if you had given a small extract of your data sheet. let us assume your data sheet is like this

Book2
ABCDEFG
211a1qz1
302s2wx2
412s2wx3
503d3ec4
613d3ec5
714f4rv6
815g5tb7
906h6yn8
1016h6yn9
Sheet1

if this is the type of data you can just do autofilter
highlight the data . click data-filter-autofilter .
click the small arrow in column A and click 1
you get filtered data. you can select the filtered data and copy paste to any other area or sheet.
after copying again click data-filter-autofiler.
the filter is removed and original data has come

Is this not what you want??????

Replies
1
Views
98
Replies
1
Views
111
Replies
1
Views
129
Replies
10
Views
137
Replies
13
Views
232

1,191,228
Messages
5,985,385
Members
439,961
Latest member
drose1105

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

### Which adblocker are you using?

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

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