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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,156
Messages
5,509,489
Members
408,738
Latest member
mkarhoff

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top