combine spreadsheets to find unique items?

  • Thread starter Thread starter Tag
  • Start date Start date
T

Tag

Hi, my question is a little weird but i can explain it hopefully well
enough that someone can help me. At y workplace we have approx 400
computers to monitor. We have 3 seperate systems to monitor these
computers, and every computer must be reporting in to these 3 seperate
systems for us to deem them secure. If they are not reporting in then
i have to reimage them to get them working again. Now what i have done
is exported excel spreadsheets from each of these programs of all the
current computers reporting in to each. What i wish to do is find a
way to combine these three sheets and compare each of the computer
id's. If an id does not appear three times flag it somehow and make it
so i know that it has to be reimaged. Does anyone know of a way i
could accomplish this?
 
So, you have 3 sheets in an Excel workbook (or is it 3 separate
files?) and each sheet can have up to 400 IDs, and you want to check
which IDs are missing from each sheet?

Is this correct? Presumably you have a master list of IDs?

Pete
 
Assuming this would all break down to one sheet with four columns, titled
ComputerID, System1, System2, and System3, in A1:D1

If so, then in E1 put something like this, and copy down........
=IF(AND(A2=B2,B2=C2,C2=A2),"ok","REIMAGING REQUIRED")

If the respective ID's are not located in the same rows, then some sort of
VLOOKUP would be a possibility.

Vaya con Dios,
Chuck, CABGx3
 
Sorry, my bad...... the formula should go in cell E2 and copy down, not E1.

Vaya con Dios,
Chuck, CABGx3
 
see this is the problem, we dont have a master list, and right now
these three spreadsheets are all sepearte files. And they are not in
order because some are missing, so while they may all have the first
10 computers in order, the 2nd sheet may be missing the 1th computer
that the other 2 have and now there will be inconsistency, understand?
 
So, you need to compile a master list of IDs by copying the IDs from
each of your 3 files into one composite worksheet (one under the
other, so you have about 1200 rows of data in column A). Make sure you
have a heading in A1 (eg master_ID) and highlight all the data plus
the header then click on Data | Filter | Advanced Filter - in the pop-
up you should check Unique Records Only together with Copy to Another
Location (in the Copy To panel enter C1) then click OK.

You will now have a reduced set of data in column C, approx 400 IDs,
and you can now delete columns A and B and save the file as
Master_ID.xls. If there is a possibility that a PC would not appear in
any of the 3 sheets, then clearly its ID will not appear in this list,
so you might have to add some yourself manually. You might want to add
some more data t this list in other columns, eg the location of the
PC, department, age, memory, HD size etc, and you might want to sort
this data before saving again.

Now you can make use of 3 other columns as Chuck suggested, for
System_1, System_2 and System_3 and use a vlookup formula to see if
the ID exists in each of the other files. Something like the
following:

=VLOOKUP(A2,'[full_path_and_filename.xls]Sheet1'!A:A,1,0)

for each of your 3 files. If you copy this down each column it will
show #N/A for any IDs which are not present in the file. It would be
easier to just copy each of the sheets from your other files into the
master_ID file, then you would not have to put in the
[full_path_and_filename.xls].

Hope this helps.

Pete
 
Back
Top