Excel Help [Solved, please close]

Cog · December 2013

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 · December 2013

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

Not elegant but I think that might work.

kaliyama · December 2013

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

Orogogus · December 2013

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 · December 2013

kaliyama wrote: »

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

Got a formula error on this.

Cog · December 2013

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 · December 2013

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 · December 2013

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

L Ron Howard · December 2013

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 · December 2013

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 · December 2013

#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 · December 2013

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 · December 2013

Awesome, got it. Thanks all! Close please!

Penny Arcade

Quick Links

Excel Help [Solved, please close]

Posts