Club PA 2.0 has arrived! If you'd like to access some extra PA content and help support the forums, check it out at patreon.com/ClubPA
The image size limit has been raised to 1mb! Anything larger than that should be linked to. This is a HARD limit, please do not abuse it.
Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

Excel deleting duplicates

solsovlysolsovly Registered User regular
edited November 2006 in Help / Advice Forum
Was wondering if anyone had a solution for me.

I have two colum of numbers (both in the hundreds). The first column (a) countains the case numbers that have been assigned to me. The second column (b) countains the case numbers I have already completed.

I would like to delete all the values in column A that are present in colum B. Essentially, I just want to get rid of the case numbers in my todo list (A) that I've already completed (b).

Couldn't find any solutions for this online. I can only find filtering unique records, but it still shows the first occurence of that record.

XBOX Live Tag: Solsovly
solsovly on

Posts

  • YourFatAuntSusanYourFatAuntSusan Registered User
    edited November 2006
    I am not sure of deleting the duplicates short of using VB, but you can try doing a VLOOKUP.

    Column A1:Awhatever - Assigned Numbers
    Column B1:Bwhatever - Completed Numbers
    Column C1:Cwhatever - Vlookup data

    Select C1 and hit insert function (left of the formula bar) and type vlookup.
    In lookup value, select all of your numbers in column a. In table array, select all of the numbers in column B. In col_index_num type 1 and in range_lookup type False. Hit OK.

    Copy the formula down through all the cells in column c (with the little black square in the bottom right of C1).

    It will list all of the duplicates and then you can cull them out.

    Hope this isn't retarded sounding.

    YourFatAuntSusan on
    [SIGPIC][/SIGPIC]
  • FeralFeral MEMETICHARIZARD along with you if I get drunk well I know I'm gonna be gonna be the man whoRegistered User regular
    edited November 2006
    Create the following formula in cell C1:
    =MATCH(A1,B:B,0)
    

    This will check to see if the contents of cell A1 are also present anywhere in column B. If the result is false, you will receive #N/A in cell C1. If the result is true, you will receive a numeric value. (The numeric value is not important.)

    Fill the formula down column C to the end of your list. Every value in your list which is not present will be flagged #N/A. Select all three columns, then sort the data by column C. Delete all rows which have a numeric value in column C.

    Does that make sense?

    Feral on
    every person who doesn't like an acquired taste always seems to think everyone who likes it is faking it. it should be an official fallacy.
    the "no true scotch man" fallacy.
  • solsovlysolsovly Registered User regular
    edited November 2006
    Thank you for the quick help guys. That solution worked perfectly.

    solsovly on
    XBOX Live Tag: Solsovly
This discussion has been closed.