Removing duplicate entries?

hrithik

Active Member
Joined
Jul 26, 2010
Messages
336
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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
1uniquemember_idnamephoneemail-idaddressdate
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??????
 

Watch MrExcel Video

Forum statistics

Threads
1,133,715
Messages
5,660,450
Members
418,580
Latest member
JP82

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
Top