Compare 2 Inventory Worksheets, Then Create Changes Worksheet

[Dex]

Hi Everyone,

I have two inventory worksheets (within the same workbook), from different months, that I need to compare and see what has increased and decreased. Normally I work with 300-400 rows of items, though I have included a sample of 5 rows here.

I would like to compare the two inventories, find what positions have changed and then list those changes (amount of increase of decrease) on a third worksheet. Ideally, I would like a macro that would use the unique ID from Column A to match between worksheets, then determine what the change in inventory amount from column E is (positive or negative). If the description, version and expire date information from Column B,C & D could be included on the third (Changes) worksheet, that would be great.

It has been a while since I have used macros and VBA, though I am comfortable with using them (and a little excited to use them again, and save some time too). Thank you in advance and please let me know if there is anything else you need from me.

Thanks again,
Dex

[watersev]

hi, what to do if id is absent and what sheet is the base for compare

[Dex]

hi, what to do if id is absent and what sheet is the base for compare

There are no blanks in the columns being compared (ID in column A, and Amount in Column E).

I would like to use the unique ID in Column A (in both Nov & Dec worksheets) as the base to compare, then see what the differences in Amount in Column E is.

Thanks for the help.

[watersev]

ok, can you please also post the result sheet you'd like to get as a result (basing on your sample data)

[Dex]

The result sheet has been included in the xls file in my original post.

Thanks for reminding me to include it!

[watersev]

see attachment

Preparation to run macro "Compare":

1. [Alt+F11] - open Visual Basic window
2. Tools - References - find and tick "Microsoft Activex Data Objects 2.0 Library"
3. Press "Ok"
4. Close Visual basic window

[Dex]

Thank you very much watersev!

How would I go about expanding the macro to work with the larger inventory (with almost 400) line items?

Thank you again!

[watersev]

it is working for 65500 rows (you can check that in the code, it's very small)

[Dex]

You are correct and that works flawlessly with the full inventory! Thank you very much!

[alsacaju]

I tried to run this macro in Excel 2003 but I’m getting the following error:
“Run-time error ‘-2147217865 (80040e37)’:
The Microsoft Jet database engine could not find the object ‘Dec 31$A2:E65500’. Make sure the object exists and that you spell its name and the path name correctly.
Is there something I need to change?

Thank you

[arlu1201]

alsacaju,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[alsacaju]

oops!! sorry...