software

Hello,
I'd like to search for values that appear in more than one workbook.
Is there ANY way that I can do this? For example, everyday I complile a
list of alpha/numeric numbers amp; save each list as a seperate file for
ease of review. I would like to be able to find if the same alpha or
numeric value is being multiple times each week. Any help is very much
appreciated. Thanks--
phreshjive
------------------------------------------------------------------------
phreshjive's Profile: www.excelforum.com/member.php...oamp;userid=30842
View this thread: www.excelforum.com/showthread...hreadid=505088See Stephen Bullen's MVP page
FindLink by Bill Manville is useful for many similar things
FlexFind by Jan Karel Pieterse is probably what you need for your problem
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;phreshjivequot; gt; wrote in message
news
gt;
gt; Hello,
gt; I'd like to search for values that appear in more than one workbook.
gt; Is there ANY way that I can do this? For example, everyday I complile a
gt; list of alpha/numeric numbers amp; save each list as a seperate file for
gt; ease of review. I would like to be able to find if the same alpha or
gt; numeric value is being multiple times each week. Any help is very much
gt; appreciated. Thanks
gt;
gt;
gt; --
gt; phreshjive
gt; ------------------------------------------------------------------------
gt; phreshjive's Profile: www.excelforum.com/member.php...oamp;userid=30842
gt; View this thread: www.excelforum.com/showthread...hreadid=505088
gt;
There are two approaches. For both, my tests worked only when all
workbooks were open.

First approach: assuming that your workbooks are all named with
consecutive dates. Further assuming that all sheets have the same name,
Sheet1 and the data are in column A:A. Let us say you need to sum five
consecutive dates, from 1/23/2006 to 1/27/2006. Let us further say that
you save your workbooks like 1-26-2006.xls

=SUM(COUNTIF(INDIRECT(quot;'[quot;amp;TEXT(DATE(2006,1,22) ROW(1:5),
quot;m-d-yyyyquot;)amp;quot;.xls]Sheet1'!A:Aquot;), _value_))

This is an array formula (enter with Shift Ctrl Enter). The expression

DATE(2006, 1, 22)

must be one date earlier than the dates you need to sum. The format
string:

quot;m-d-yyyyquot;

should be changed to reflect the format you actually use. Finally, in
the expression

ROW(1:5)

you need to change the 5 to whatever number of workbooks you are
attempting summation. Of course, instead of _value_ supply the value
that you want or a cell reference.

The second approach is more powerful, it allows you to go over any set
of workbooks (again, however, they must be open). Use a range (say
Sheet2!$K$1:$K$6). In the first cell (K1) type a label (e.g. workbooks)
and in the remaining cells (K2:K6) type the names of the workbooks (do
not forget the .xls).

=SUM(COUNTIF(INDIRECT(quot;'[quot;amp;T(OFFSET(Sheet2!$K$1, ROW(1:5),
0))amp;quot;]Sheet1'!A:Aquot;), _value_))

again you need to array-enter it.

HTH
Kostis Vezerides