Excel 2010 and Duplicate entries

Discussion in 'Software' started by twizzle, Jul 31, 2012.

  1. twizzle

    twizzle Gigabyte Poster

    1,842
    43
    104
    Guys i'm in desperate need of help.

    I have a spreadsheet with loads of columns one of which is Serial numbers. I need to remove duplicate serials from that column and paste into another sheet along with other columns. However i cant seem to do this. The Remove duplicate option in 2010 does exactly that, removes them leaving no option to paste them into another sheet. If i try conditional formatting to highlight duplicates, then sort so their at the top, cut and paste to new sheet, it gives a different total to the figure given in remove duplicates.

    If i just copy the serial number column to new sheet then remove duplicates, i get yet a third figure. (This is also no good as i need other columns to be brought across linked to the serials). There just seems to be no consistancy to whats duplicated using different methods of removing or highlighting them.

    So can anyone come up with a solutions where i can remove my duplicates and have them pasted along with other associated columns to a new sheet?

    Thanks in advance!
     
    Certifications: Comptia A+, N+, MS 70-271, 70-272
    WIP: Being a BILB,
  2. nXPLOSi

    nXPLOSi Terabyte Poster

    2,874
    30
    151
    This is something I have had to do a few times, and to be honest, as basic as this might sound its always worked for me.

    I just do something like =IF(A1=A2,"Match","x"), run that down so all the duplicates then show "Match", and then copy and "Special" paste with "Values" the whole spreadsheet. Then sort by the column that says "Match" or "X" and them just cut and paste the "Matched" ones into a second spreadsheet.

    Hope that makes sense and gives you what your looking for.

    (I'm sure theres a proper technical formulated way of doing all this but it works for me!!)
     
    Certifications: A+, Network+, Security+, MCSA 2003 (270, 290, 291), MCTS (640, 642), MCSA 2008
    WIP: MCSA 2012
  3. twizzle

    twizzle Gigabyte Poster

    1,842
    43
    104
    I'm not sure i follow this at all to be honest.

    basically i have 1 col thats serials so say A1 to A1500 but the 2nd col might be model so cells A2 to A1500. Looking at your formula A1 would never equal A2 as they have different values all the time (one numerical the other txt.) Plus is MATCH and actual command or to be replaced with something such as a value?

    Sorry if i appear dumb on this but looking at figures all morning is now starting to turn me even more insane!
     
    Certifications: Comptia A+, N+, MS 70-271, 70-272
    WIP: Being a BILB,
  4. Notes_Bloke

    Notes_Bloke Terabyte Poster

    3,230
    54
    146
    Hi mate,

    Don't know if this app will do what you want. It's not free, but it has a 15 day trial version.

    NB
     
    Certifications: 70-210, 70-215, A+,N+, Security+
    WIP: MCSA
  5. nXPLOSi

    nXPLOSi Terabyte Poster

    2,874
    30
    151
    Match is not a command of any sort, its just the result of the formula so you know that you have a match (Duplicate).

    If you are comparing rows, say A1 and A2, to see if they are the same, order by the serial number and then enter the formula =IF(A1=A2,"Match","x") in say B1 and run it down all the rows. This will show "Match" where you have a duplicate. If you then copy and Edit -> Paste Special and select "Values", it will paste the spreadsheet without the formulas, so you can then sort by B1. The matches will then jump to the top, and you know these are the duplicates and can therefore paste them onto your second spreadsheet.

    I have attached an example where I inserted a row in front of the serial number, and used the above method, hopefully that will get you what your looking for.

    View attachment Example.xls
     
    Last edited: Jul 31, 2012
    Certifications: A+, Network+, Security+, MCSA 2003 (270, 290, 291), MCTS (640, 642), MCSA 2008
    WIP: MCSA 2012
  6. Coupe2T

    Coupe2T Megabyte Poster

    590
    43
    67
    This sound like something where VLOOKUP could be used to pull any duplicates to a new page, not 100% sure, been a long time since I used vlookup, but google it and see if it can help you, plenty of examples out there I am sure.
     
    Certifications: ECDL, Does that Count!?!
  7. twizzle

    twizzle Gigabyte Poster

    1,842
    43
    104
    Thanks for the help guys. In the end i just used conditional formatting to highlight the dupes, then cut and pasted them to another sheet along with the rest of thier row. It just seems strange that i could get different results using remove duplicates (sometimes 792, or 791 and other times 783) dependant on if i selected teh title cell (which was just one extra cell) than if i just selectedall teh cells underneath it, and also get different number if i cut the column out and pasted into another sheet then used remove dupes, (560 or 498). There just seemed no resaon for the differing figures.
     
    Certifications: Comptia A+, N+, MS 70-271, 70-272
    WIP: Being a BILB,

Share This Page

Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.