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 & 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: http://www.excelforum.com/member.php...o&userid=30842
View this thread: http://www.excelforum.com/showthread...hreadid=505088

See 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: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"phreshjive" wrote in message
...

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 & 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: http://www.excelforum.com/member.php...o&userid=30842
View this thread: http://www.excelforum.com/showthread...hreadid=505088

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("'["&TEXT(DATE(2006,1,22)+ROW(1:5),
"m-d-yyyy")&".xls]Sheet1'!A:A"), _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:

"m-d-yyyy"

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("'["&T(OFFSET(Sheet2!$K$1, ROW(1:5),
0))&"]Sheet1'!A:A"), _value_))

again you need to array-enter it.

HTH
Kostis Vezerides