Excel deleting duplicates

solsovly

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.

YourFatAuntSusan

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.

Feral

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?

solsovly

Thank you for the quick help guys. That solution worked perfectly.