Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidating Data
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
consolidating data | Excel Discussion (Misc queries) | |||
Consolidating Data | Excel Discussion (Misc queries) | |||
Consolidating Data | Excel Worksheet Functions | |||
Merging/Consolidating data? | Excel Discussion (Misc queries) | |||
Consolidating data?? | Excel Worksheet Functions |