software

Hi there,

I have two seperate worksheets, one contains external data related to all
installs of our product in our customers stores. The other spreadsheet is an
internal worksheet with all the information we have on all the stores. I need
to see if there are any stores listed on the external worksheet that are not
currently on our internal worksheet so that I can then copy the information
over.

Both worksheets use the same identifier and this is what I am trying to do
the comparison on.

I was told that the IF function could be used to do this, however, I cant
get it to work.

Any ideas?


Laura,

Try this in your external data sheet. Insert a column to the right of
your identifying column. In this case I used A as the Company
identifier.

=IF(ISERROR(INDEX(Internal!$A$1:$A$11,MATCH(extern al!A3,Internal!$A$1:$A$11,0))),quot;Not
Foundquot;, quot;Foundquot;)

This will put Found or Not Found based on your list of internal id's.
If you want, you can then conditionally format the identifiers to color
the row if Not Found is populated. Select the entire row and
Formatgt;Conditional Formatgt;

Formula is: =$B2=quot;Not Foundquot;, select your formatting. Use the format
painter to copy this down all of your rows making it easier to identify
the missing data.

HTH

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=513377I would bet that the =match() portion is the part that's doing all the real
work.

maybe just checking that would be sufficient:

=if(iserror(match(external!a3,internal!$a$1:$A$11, 0)),quot;Not foundquot;,quot;Foundquot;)

SteveG wrote:
gt;
gt; Laura,
gt;
gt; Try this in your external data sheet. Insert a column to the right of
gt; your identifying column. In this case I used A as the Company
gt; identifier.
gt;
gt; =IF(ISERROR(INDEX(Internal!$A$1:$A$11,MATCH(extern al!A3,Internal!$A$1:$A$11,0))),quot;Not
gt; Foundquot;, quot;Foundquot;)
gt;
gt; This will put Found or Not Found based on your list of internal id's.
gt; If you want, you can then conditionally format the identifiers to color
gt; the row if Not Found is populated. Select the entire row and
gt; Formatgt;Conditional Formatgt;
gt;
gt; Formula is: =$B2=quot;Not Foundquot;, select your formatting. Use the format
gt; painter to copy this down all of your rows making it easier to identify
gt; the missing data.
gt;
gt; HTH
gt;
gt; Steve
gt;
gt; --
gt; SteveG
gt; ------------------------------------------------------------------------
gt; SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
gt; View this thread: www.excelforum.com/showthread...hreadid=513377

--

Dave Peterson