Coding problem

Hello.

I'm no expert on all this so please forgive the obvious :-)

I have a spreadsheet with 2 worksheets. The main sheet has several columns
that do SQL Lookups and populate columns with PC names dependent on
criteria. (Each column lists PCs with a software product installed. These
columns are dynamically updates agains an SMS 2003 reporting database.
(Replicated copy of live data, replicates weekly)

The front sheet also does an SQL Lookup of the database and lists all the
PCs SMS is aware of. I would like to put a tick (Wingdings 2 capital P!!)
against a PC name in the First tab if it has a given piece of software
installed.

So what I have is on one sheet a list of PCs and on the other a list of PCs
with given software installed. I want to compare the 2 lists and if a name
appears on both lists, tick a column on the front sheet. Each piece of
monitored software has a column on both the First and second sheets.

Can anyone give me any pointers on how to get this thing going? Has it
already been done? Is there a pre-existin formula or function that could do
this?

Any help greatly appreciated.

Regards,

John

Assuming the PCs are both in column A, on the first

B2: = IF(ISNUMBER(MATCH(A2,Sheet2!A:A,0)),"a",""

format B2 as Marlett, and copy down.

--
HTH

Bob Phillips

"John" <hush@dontspamme.com> wrote in message
news:%23YvfDO0dFHA.2180@TK2MSFTNGP12.phx.gbl...
> Hello.
>
> I'm no expert on all this so please forgive the obvious :-)
>
> I have a spreadsheet with 2 worksheets. The main sheet has several columns
> that do SQL Lookups and populate columns with PC names dependent on
> criteria. (Each column lists PCs with a software product installed. These
> columns are dynamically updates agains an SMS 2003 reporting database.
> (Replicated copy of live data, replicates weekly)
>
> The front sheet also does an SQL Lookup of the database and lists all the
> PCs SMS is aware of. I would like to put a tick (Wingdings 2 capital P!!)
> against a PC name in the First tab if it has a given piece of software
> installed.
>
> So what I have is on one sheet a list of PCs and on the other a list of
PCs
> with given software installed. I want to compare the 2 lists and if a name
> appears on both lists, tick a column on the front sheet. Each piece of
> monitored software has a column on both the First and second sheets.
>
> Can anyone give me any pointers on how to get this thing going? Has it
> already been done? Is there a pre-existin formula or function that could
do
> this?
>
> Any help greatly appreciated.
>
> Regards,
>
> John
>
>
>

This comes from a post a few questions down regarding comparing two workbooks.
I think it would be beneficial to you and solve your problem.

Credit goes to Tom Ogilvy in this situation.
Your "flag" would be the existence of a number other than zero in the column
next to your computer list. So, if you have a list of computers, then a
column for each software package, the Countif statement would refer to the
page that has the software/computer number. There are other Count like
statements that can look through a range and count if they meet a given
value, or if they are empty, etc...

As far as indicating a particular character, if you use an if statement you
can have a column such that

If(CountIf(....) <> 0, "(your marker)", "")
(I am not sure what results from a Countif if there is nothing found, but
whatever the particular result on your spreadsheet, that is the comparison to
be made, so if it is "" then countif(....) <> "". Etc...



in an adjacent column

=Countif([Book2.xls]Sheet1!A:A,A1)

drag down the column

repeat in Book2 checking Book1.

then you can filter on the results.

See Chip Pearson's site for working with duplicates and uniques:

http://www.cpearson.com/excel/duplicat.htm

--
Regards,
Tom Ogilvy


"John" wrote:

> Hello.
>
> I'm no expert on all this so please forgive the obvious :-)
>
> I have a spreadsheet with 2 worksheets. The main sheet has several columns
> that do SQL Lookups and populate columns with PC names dependent on
> criteria. (Each column lists PCs with a software product installed. These
> columns are dynamically updates agains an SMS 2003 reporting database.
> (Replicated copy of live data, replicates weekly)
>
> The front sheet also does an SQL Lookup of the database and lists all the
> PCs SMS is aware of. I would like to put a tick (Wingdings 2 capital P!!)
> against a PC name in the First tab if it has a given piece of software
> installed.
>
> So what I have is on one sheet a list of PCs and on the other a list of PCs
> with given software installed. I want to compare the 2 lists and if a name
> appears on both lists, tick a column on the front sheet. Each piece of
> monitored software has a column on both the First and second sheets.
>
> Can anyone give me any pointers on how to get this thing going? Has it
> already been done? Is there a pre-existin formula or function that could do
> this?
>
> Any help greatly appreciated.
>
> Regards,
>
> John
>
>
>
>

Typo

=IF(ISNUMBER(MATCH(A2,Sheet2!A:A,0)),"a","")

--
HTH

Bob Phillips

"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:O%23mFQX0dFHA.584@TK2MSFTNGP15.phx.gbl...
> Assuming the PCs are both in column A, on the first
>
> B2: = IF(ISNUMBER(MATCH(A2,Sheet2!A:A,0)),"a",""
>
> format B2 as Marlett, and copy down.
>
> --
> HTH
>
> Bob Phillips
>
> "John" <hush@dontspamme.com> wrote in message
> news:%23YvfDO0dFHA.2180@TK2MSFTNGP12.phx.gbl...
> > Hello.
> >
> > I'm no expert on all this so please forgive the obvious :-)
> >
> > I have a spreadsheet with 2 worksheets. The main sheet has several
columns
> > that do SQL Lookups and populate columns with PC names dependent on
> > criteria. (Each column lists PCs with a software product installed.
These
> > columns are dynamically updates agains an SMS 2003 reporting database.
> > (Replicated copy of live data, replicates weekly)
> >
> > The front sheet also does an SQL Lookup of the database and lists all
the
> > PCs SMS is aware of. I would like to put a tick (Wingdings 2 capital
P!!)
> > against a PC name in the First tab if it has a given piece of software
> > installed.
> >
> > So what I have is on one sheet a list of PCs and on the other a list of
> PCs
> > with given software installed. I want to compare the 2 lists and if a
name
> > appears on both lists, tick a column on the front sheet. Each piece of
> > monitored software has a column on both the First and second sheets.
> >
> > Can anyone give me any pointers on how to get this thing going? Has it
> > already been done? Is there a pre-existin formula or function that could
> do
> > this?
> >
> > Any help greatly appreciated.
> >
> > Regards,
> >
> > John
> >
> >
> >
>
>