Searching multiple workbooks
 

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

Searching multiple workbooks
 

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

Searching multiple workbooks
 

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