Subtracting data from a list?

**Blue Angel** · 02-23-2011, 09:18 AM

Hi all, new member here.

I'm working with a query tool that generates lists of data and exports to Excel worksheets. A certain query I'm working on right now returns a list of over 2700 line items, and each of these items needs to be examined manually...

Based on a search criteria, I'm able to use a filter to query a list of over 700 items that are part of the original query, but do not need any attention.

Question: How can I subtract the ~700 items in the second query from the original query list? Each query exported to Excel generates a new worksheet. No, the query tool does not have the ability to filter out these line items on its own.

Thanks!

P.S. I'm not a deeply skilled user, so I need things in layman's terms!

EDIT: Each query outputs a new workbook, not a worksheet.

**Blake 7** · 02-23-2011, 09:25 AM

Hi there Blue and welcome to the forum.

Sometimes (and this is one of them) it would be of great use to upload a sample of your data. Make sure you demonstrate your desired output (ie where youwant the results to go and what they should look like)

from the sounds of it you may need lookup, index match, ect ect....! if you upload- you will be sure to achieve your desired result.

**Blue Angel** · 02-23-2011, 09:38 AM

Thanks for the quick reply!

Because of the nature of the data I'm working on I can't actually upload anything real, but I can give you a very simple scenario that hopefully will suffice.

I have Excel open and have two workbook windows visible, book1 and book 2.

Book 1 is a list of numbers in column A:

1
2
3
4
5
6
7
8
9
10

Book 2 is also a list in column A:

2
3
6
8
9

I need Excel to subtract the line items from book 2 from those in book 1 so I have:

1
4
5
7
10

This is a very basic example, and in my case each of these line items is a "part number" that has 24 columns of data next to it. If it's easier to work "sheet to sheet" than "book to book" the data in book 2 could easily be copied to a sheet in book 1.

**Blake 7** · 02-23-2011, 11:51 AM

not quite sure I get it - also, as I previously mentioned, a workbook sample is ALWAYS better, as if your helper wants to test something he/she has to type out the data for his/her self unless its a guru helping and they dont need to use a spreadsheet.

Do you want a simple list of part numbers that appear in book one which don't appear in book 2?

**Blue Angel** · 02-23-2011, 02:50 PM

Originally Posted by Blake 7

Do you want a simple list of part numbers that appear in book one which don't appear in book 2?

Yes, exactly! Any numbers appearing in book 2 are numbers that already exist in book 1 and don't need to be there; removing them (or filtering them out somehow) will reduce the size of a HUGE task.

I'm working with a very simple query tool that has almost NO flexibility when it comes to sorting/limiting its output.

**Blake 7** · 02-23-2011, 03:37 PM

hiya - going to put my son to bed.

I'll show you a way to achieve this in an hour (i hope)

**Blake 7** · 02-23-2011, 05:08 PM

Hiya - please see the attachment.

Method 1 =MATCH(A2,B:B,0) where an #NA appears it means that the part num in book 1 doesn't appear in book 2.

Method 2 =COUNTIF(B:B,A2) where a 0 appears it means that the part num in book 1 doesn't appear in book 2.

Method 4 =ISERROR(MATCH(A1,B:B)) with this one you put into the conditional formatting and it will highlight in colour anything in book one not in book two.

Once you have done this you can simply filter on the #NA's or 0's ect and cut or paste,

Is this what youare after?