Comparing data in multiple spreadsheets with the first being the m

L

LindaR

I have been tasked with creating an inventory of all my wireless devices. I
have a master started, but I get the monthly data on a spreadsheet from
multiple sources. I know there is a way to preserve my master inventory and
compare the new monthly data to show anything that is either added or
deleted, but I am only a "basic" Excel user and would like to know how to do
this. Thanks!
 
O

Otto Moehrbach

Linda
From what you say, you will probably need VBA programming for this. You
will have to provide a lot more information about what you have and what you
want to have. A good way to do this would be for you to write down a
step-by-step procedure of what you would do if you had to do this in Excel
manually, that is, without programming. Include information about the
layout of what you have and what you want to have. Like, this is in this
column, that is in that column, etc. Explain it like you would for someone
who knows nothing about your project. HTH Otto
 
L

LindaR

Otto,

Okay I will try and make this easy to understand without taking up too much
space. I have a master spreadsheet of my inventory, column A is the phone
number and column B is the user name. Each month I get a new spreadsheet with
the current users, so some have been added and some have been canceled. Right
now what I do is sort both spreadsheets by the phone number, put the master
and the current list in "side by side" view, then I scroll down through both
of the sheets one line at a time to find the ones that are either new (not on
the master) or "gone" from the list (still on the master but not on the
current bill). I have 4 different vendors to work through and 2 of the
spreadsheets has anywhere from 1200 to 1450 rows of data.

Thank you for your assistance. Linda
 
O

Otto Moehrbach

Linda

You did a good job with that. I have a couple of questions. Your
Master. Is that a workbook with just that one sheet?

The new spreadsheet. Is that a separate workbook (wb) and you work with it
as a separate workbook or do you copy the sheet(s) from it into the Master
and work with it that way?

You say you have 4 different vendors. How does that play into
what you do? Do you have a "new workbook" that comes in once a month for
each vendor?

The new workbooks. Do they always have the same names? Do you
put them in one folder?

I take it that you use the phone numbers to compare with. What
do you want as the final product? In other words, some entries in the new
wb are new (not in the Master) and some entries in the Master are not in the
new wb. The code (VBA programming) will find all this but how do you want
it to tell you what it finds? Do you want a list of all the old and all the
new? If so, where? Do you want the old to turn one color and the new to
turn another color? Maybe you want all the new placed in the Master. If
so, what do you want to happen with the old?

The code can work with any layout you have but I recommend that
you copy sheets so that you end up with one wb containing the Master sheet
and all the vendor sheets. Then you would run the code and it would do all
at once.

If you wish, it might be better if you send me a copy or a
sample of your Master and the other workbooks. Make up fake data if you
wish. I need just the layout. My email is [email protected].
Remove the "extra" from this address. HTH Otto
 
O

Otto Moehrbach

Linda
I haven't heard back from you. If you want to work this through the
newsgroup, just give me the info I asked for and we'll do that. Otto
 
L

LindaR

Hi Otto,

I am still trying to get this resolved, but I was not able to get back to
the newsgroup....busy with other stuff! Thanks so much for your input. It
sounds like you will be able to help me with this once I send you the data
you asked for and answer your questions. I will get back to that soon, very
soon! Linda
 
Top