Excel Help [Solved, please close]

Cog

I have a spreadsheet with a whole shitload of serial numbers.

I have another spreadsheet with a smaller shitload of serial numbers that are a derived subset of the first sheet.

Is there a way to get excel to use one list to remove those entries from the initial list? I want to easily get a list of serials that AREN'T in the subset.

DevoutlyApathetic

Errr...something like using VLOOKUP, then sorting out the non-failing responses?

Not elegant but I think that might work.

kaliyama

http://stackoverflow.com/questions/10399060/excel-two-columns-with-duplicates

Orogogus

Depending on what version of Excel you're using, there may be a MATCH() function that does this.

In a column next to the large list, set up a column of =MATCH([adjoining cell], [the entire range of the long list], 0)

The 0 at the end indicates you want an exact match.

Once that's done, the list should be populated mostly with #N/A values. Copy, Paste Special as values, then sort by that column. The #N/As are the ones that aren't in the subset.

Cog

kaliyama wrote: »

http://stackoverflow.com/questions/10399060/excel-two-columns-with-duplicates

Got a formula error on this.

Cog

Orogogus wrote: »

Depending on what version of Excel you're using, there may be a MATCH() function that does this.

In a column next to the large list, set up a column of =MATCH([adjoining cell], [the entire range of the long list], 0)

The 0 at the end indicates you want an exact match.

Once that's done, the list should be populated mostly with #N/A values. Copy, Paste Special as values, then sort by that column. The #N/As are the ones that aren't in the subset.

Hrm... error again. These are serial numbers and have both letters and numbers in them, so maybe the Match function doesn't work against non-number entries?

EDIT: Thanks for the input everyone, i'll have to pick this up in the morning.

Orogogus

What kind of error? You want #N/A errors, but a #NAME error would mean it's not supported in your version of Excel. It totally works with non-numeric values, though.

Cauld

paste the 2nd list below the first list and then remove duplicates?

L Ron Howard

Copy everything into a separate sheet, then use something like this (Stolen from someplace on Google) :
=IF(ISERROR(MATCH(Sheet2!A15,Sheet1!A:A,0)),"No Match",Sheet2!A15)

That will get it to print the duplicates, I think. From there, you can make a Pivot Table to display or not display the duplicates.

Cog

Orogogus wrote: »

What kind of error? You want #N/A errors, but a #NAME error would mean it's not supported in your version of Excel. It totally works with non-numeric values, though.

#N/A errors. Office Pro Plus 2010.

MrTLicious

#N/A error means that the item isn't on the list (Actually, if you're using vlookup, it means it's alphabetically before the first item on the list, you'll want to be using MATCH for this, with a 0 as the last argument, or insert false as the optional argument in vlookup).

So after you get a bunch of #N/As, sort on that formula (should probably copy/paste special values first).

Now you'll have a bunch of items and #N/As together, which are all the items you want (i.e. they are not in the 2nd, shorter list).

schuss

Also, if you're using ranges, remember to put $ before it, such as =VLOOKUP(A2, SHEET2!$A$2:$A$5013,1, FALSE), as otherwise it will move your range on you. You don't need to do this if you're just selecting a column (=VLOOKUP(A2, SHEET2!A:A,1, FALSE)). Once you get the matches, just filter and remove.

Cog

Awesome, got it. Thanks all! Close please!