Deleting Repeated data
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Deleting Repeated data

  1. #1
    Guest

    Default

     
    Hello people
    I have two columns of repeated data like the following
    1 aa
    1 aa
    2 bb
    2 bb
    2 bb
    3 cc
    3 cc
    and I am trying to delete the repeated ones and leave only just one data like
    1 aa
    2 bb
    3 cc
    So is there any quick way to do that?

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Once and done or do you need a macro to do it every day/week...?
    ~Anne Troy

  3. #3
    Guest

    Default

    I have to do three spreadsheets but only do them once
    Can you help?
    Thank you

  4. #4
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Assuming you really only have 2 columns:

    In c1, type: Dupes
    In c2, type: =a2&b2
    In c3, type: =if(a3&b3=c2,"dupe","")

    Copy c3 down all the rest of the rows.

    Select only the populated cells in column 3. Copy them. Don't move cursor. Hit Edit-Paste special, Values, Ok.
    Click on C2 and hit the ZA (descending) sort button. Delete all rows that say *dupe*. Re-sort how you like.
    ~Anne Troy

  5. #5
    Guest

    Default

    sorry a stupid question
    how do you do
    Copy c3 down all the rest of the rows.

  6. #6
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    After entering the formula in C3, hit Enter. Then click on C3. You'll see a little black blob in the bottom, right-hand corner of the black border that tells you the cell is selected.

    Double-click that black blob. If it doesn't do anything, click on that bottom, right-hand corner and drag down until you're at the end of the cells in column B and let go.

    That little black blob (or square) is called the fill handle.

    You could also select C3 and then all the cells below it. Then hit Edit-Fill, Down.
    ~Anne Troy

  7. #7
    Guest

    Default

    I see how it works
    but for some reason, my if formula doesn't work after the second row
    it gives me "" instead of dupe which is what it suppose to be

  8. #8
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It's ONLY supposed to be dupe if it's a duplicate. If you didn't already do so (Doh! I forgot!), sort your data first by column A then B. Click in like A2, and hit Data-Sort. Choose Column A (or its heading) and *then by* Column B (or its heading). Forgot to tell you to sort first! Doh!

    Look, I'm going to bed. If you can't work it out, email it to Dreamboat@TheWordExpert.com and I can send it back to ya first thing in the morning.
    ~Anne Troy

  9. #9
    Guest

    Default

    yeah I got it
    I need to put the if statement in column D to get the proper dupe/" "
    and =A2&B2 in column C
    Thanks a lot DreamBoat
    Goodnight

  10. #10
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com