As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

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.

solsovly on

Posts

  • YourFatAuntSusanYourFatAuntSusan Registered User regular
    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 interior crocodile alligator ⇔ ǝɹʇɐǝɥʇ ǝᴉʌoɯ ʇǝloɹʌǝɥɔ ɐ ǝʌᴉɹp ᴉRegistered 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
This discussion has been closed.