ExcelBanter - Consolidating Data

There are some complicated and heavy lifting array formulas to do this and
create a tidy little list, and I'm sure a macro could be written to do the
same. But if you want simple worksheet functions to do this, try this common
technique.

Basically, you're going to do an INDEX/Match on each item in one list to the
other, and get a response. So for this exercise, let's assume both sheets
have the product names or codes you want to compare in column A. We'll call
the Book1 and Book2.

In Book1 in an adjacent cell (B2 maybe?) to the names/codes starting in A2,
put this formula:

=IF(ISNA(INDEX(MATCH(A2,'[Book2.xls]Sheet1'!A$2:A$1000,0),1)),"New
Item","Old Item")

And you'll get an answer for the first item. Now copy that formula down the
rest of the column and you'll get a clear listing. You can sort your entire
data table by this new column to get all the "new items" together.

Now, do the same thing on the other sheet to check it's items against the
first book.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.

"Sisell" wrote:

Hello.

I have 2 spreadsheets that I need to take certain information from each
spreadsheet to come up with a different spreadsheet or two.

For example.

The first worksheet is info regarding inventory from Branch 94 and the other
worksheet is inventory from Branch 17. Out of those 2 spreadsheets/worksheet
I need to identify new items that branch 17 has but branch 94 does not. then
what branch 94 has and branch 17 does not.