software

Here's one play using non-array formulas which might also deliver this ..

A sample construct is available at:
www.savefile.com/files/9870225
Multiple search amp; auto-extract rows from 3 sheets based on key charge code
col.xls

Assume source data is in Sheets 1 to 3 (identically structured)
data within cols A to J, from row6 down to say,
a max expected row15 (Col headers in row5)
The key charge codes are assumed in col A

In Sheet1,

Put in K6:
=IF(A6=quot;quot;,quot;quot;,IF(SUMPRODUCT(ISNUMBER(SEARCH(Summ!$A $1:$A$3,A6))*(Summ!$A$1:$A$3lt;gt;quot;quot;))gt;0,ROW(A1),quot;quot;))

Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In Sheet2

Put in K6:
=IF(A6=quot;quot;,quot;quot;,IF(SUMPRODUCT(ISNUMBER(SEARCH(Summ!$A $1:$A$3,A6))*(Summ!$A$1:$A$3lt;gt;quot;quot;))gt;0,ROW(A1) MAX(S heet1!K:K),quot;quot;))

Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In Sheet3

Put in K6:
=IF(A6=quot;quot;,quot;quot;,IF(SUMPRODUCT(ISNUMBER(SEARCH(Summ!$A $1:$A$3,A6))*(Summ!$A$1:$A$3lt;gt;quot;quot;))gt;0,ROW(A1) MAX(S heet2!K:K),quot;quot;))

Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In a new sheet: Summ,

Entry of the charge code(s) to search will be made within A1:A3 (can be in
any order). To facilitate search input(s), format A1:A3 as text (Format gt;
Cells gt; Text)
Input 2 charge codes into A1:A2, say: 1-90, 1-91

Place the same col labels in A5:J5

Then put in A6:

=IF(ISERROR(SMALL(Sheet1!$K:$K,ROW(A1))),
IF(ISERROR(SMALL(Sheet2!$K:$K,ROW(A1)-COUNT(Sheet1!$K:$K))),
IF(ISERROR(SMALL(Sheet3!$K:$K,ROW(A1)-(COUNT(Sheet1!$K:$K) COUNT(Sheet2!$K:$K)))),quot;quot;,
INDEX(Sheet3!A:A,MATCH(SMALL(Sheet3!$K:$K,ROW(A1)-(COUNT(Sheet1!$K:$K) COUNT(Sheet2!$K:$K))),Sheet3! $K:$K,0))),
INDEX(Sheet2!A:A,MATCH(SMALL(Sheet2!$K:$K,ROW(A1)-COUNT(Sheet1!$K:$K)),Sheet2!$K:$K,0))),
INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)) ,Sheet1!$K:$K,0)))

Copy A6 across to J6, fill down to cover the *total* expected range in the 3
sheets, ie to J35 (in this example, the expected max data range is: 10 rows
per sheet x 3 sheets = 30 rows)

The auto-extracted results from Sheets 1 to 3 will be returned within
A6:J35, all neatly bunched at the top. Extracted lines will be listed in the
order: Lines from Sheet1, then those from Sheet2, then those from Sheet3.
Adapt to suit ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Ainequot; wrote:
gt; Hi All,
gt;
gt; Sorry, this may seem like an elementary question but....
gt;
gt; First of all I have three seperate worksheets containing data within my
gt; workbook.
gt;
gt; I want to return all rows from each of those worksheets that matches a
gt; certain criteria to another worksheet, without having to manipulate the
gt; data.
gt;
gt; e.g.
gt; I have charge codes beginning in 1-90XX, 1-91xx, etc...
gt; These appear multiple times in the worksheets that contain data
gt; I want to search sheets 1 - 3 to see if any rows contain them amp; if they
gt; do, I want all these complete rows to appear in another worksheet
gt; within that same workbook.
gt;
gt;
gt;
gt; VLOOKUP function will not work for me as the charge code is in column O
gt; amp; I can only get it to return data within that row for column O onward.
gt;
gt; Also, I am unsure if you can get VLOOKUP to check for multiple
gt; conditions
gt;
gt; * What function should I be using?
gt; * Can you use VLOOKUP in a nested statement to search for the various
gt; conditions(charge codes)?
gt; * Can VLOOKUP return a whole row, without having to specify the column?
gt; * Can VLOOKUP return data previous to the column where the criteria of
gt; the search is met?
gt;
gt; * Finally: Should I try to use Macros amp; if so, can someone point me in
gt; the right direction???
gt;
gt;
gt; Thanks,
gt;
gt; Aine