Comparing data in multiple worksheets

[Chief1409]

Hi,
I have multiple worksheets with information on valves on a chemical plant.
Each worksheet is formatted in the same way. Each worksheet ideally should
have identical info for all the valves but this is not the case so I want to
create a master worksheet that consolidates the information from all the
worksheets. I need to first check which worksheets the valve appears on (as
not all valves appear on every worksheet) and then compare the information
from these worksheets - if the information is identical then fill this
information in the corresponding entry on the master worksheet, and if the
information is different then put something like "discrepancy" in the cell
master worksheet. A further complication is that even though a valve may
appear on a certain worksheet, the info in a particular column could be blank
and if so, this should be ignored in the comparison of data.

Any help would be much appreciated as some of the worksheets have 1000+ rows
and to compare the data manually would take weeks!

Regards,
Chief1409

 

[Dave Peterson]

When I have to do something like this, I'll try to make a consolidated list of
unique key values (Valve ID???).

I'll create a new sheet and copy the id's into column A of that new sheet (just
include a header in row 1). Continue copying at the bottom of column A for each
worksheet.

After you have all those id's (lots of duplicates and lots of singletons), you
can use data|Filter|advanced filter to eliminate the duplicates. (I'd put the
unique values in column B and delete column A).

Debra Dalgleish shows how:
http://www.contextures.com/xladvfilter01.html#FilterUR

Now depending on the fields you care about, you can use a bunch of =vlookup()'s
to return the value from each worksheet. (I usually copy each worksheet into
that same workbook--just to make it easier to work with and for safety's sake.
I don't want to damage the original data.)

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())

Then you can use different formulas to look for differences. And use =vlookup()
to bring those values back into the "master" worksheet.

 

[Chief1409]

Thanks Dave. I'm not 100% sure i was clear in my original explanation of my
problem - i've re-written it to try and make it simpler:

I have the consolidated list of unique valve numbers. But what i need to do
is compare the info and only return a single value/description into the cell
in question - this value/description is the result of the comparison of all
the equivalent cells on the original worksheets (if the equivalent cell
exists i.e. the valve may not be present on the original worksheet).

So in summary my formulas/macro/other method needs to automate the following:
1. Determine which sheets contain the valve in question
2. Compare the info e.g. in column B, for that valve on every sheet that the
valve appears (this will need to be done for around 48 columns)
3. If all column B entries for that valve are identical return that
value/description to column B for that valve on the master sheet
4. If not all column B entries match then return some text like e.g.
"discrepancy"
NOTE: Even if the valve appears on certain sheets there may not be an entry
in the column in question and if this is the case then that sheet should be
ignored in the comparison.

Hopefully this is a bit clearer, but maybe its me who didn't understand your
reply so apologies if thats the case!

 

[Dave Peterson]

I think you can use that technique that I outlined before.

After you get the unique list of valve Id's in column A, you can put the
=vlookup() formulas in columns B:X(whatever)

#1. If that formula returns #n/a, then the valve id doesn't exist on the
worksheet for that column.

#2. That's what the =vlookup()'s would do.

#3. You can use a formula like:
=if(countif(b2:aw2,b2)=48,"All the same","Some different")
B:AW is 48 columns

Then you can return that value to the master sheet.

#4. See #3.