I have a worksheet (S1) that I want to, from a separate sheet (S2) (same
workbook) search a colum in S1 and find each case where "P" occurs and return
all of the data from that "P" row. Furthermore, I want to omit the rows in
S2 where the "P" does not occur so that it will generate a compact report.
Any thoughts?

One simple set-up using non-array formulas that delivers ..

Assume source data in sheet: S1, cols A to E, data from row2 down
where the key col = col E (that's the col to monitor for the "P" values)

In sheet: S2,
In A2: =IF('S1'!E2="P",ROW(),"")
Leave A1 empty. This is the criteria col.

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX('S1'!A:A,SMAL L($A:$A,ROWS($1:1))))
Copy B2 across by 5 cols to F2. Select A2:F2, copy down to cover the max
expected extent of source data in S1. Minimize/hide col A. Cols B to F will
return only the source lines where col E = "P", with all lines compactly
bunched at the top.

Voila? Click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Hummel Guy" wrote:
I have a worksheet (S1) that I want to, from a separate sheet (S2) (same
workbook) search a colum in S1 and find each case where "P" occurs and return
all of the data from that "P" row. Furthermore, I want to omit the rows in
S2 where the "P" does not occur so that it will generate a compact report.
Any thoughts?

Awesome reply. You nailed it and I apreciate your help!

"Max" wrote:

One simple set-up using non-array formulas that delivers ..

Assume source data in sheet: S1, cols A to E, data from row2 down
where the key col = col E (that's the col to monitor for the "P" values)

In sheet: S2,
In A2: =IF('S1'!E2="P",ROW(),"")
Leave A1 empty. This is the criteria col.

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX('S1'!A:A,SMAL L($A:$A,ROWS($1:1))))
Copy B2 across by 5 cols to F2. Select A2:F2, copy down to cover the max
expected extent of source data in S1. Minimize/hide col A. Cols B to F will
return only the source lines where col E = "P", with all lines compactly
bunched at the top.

Voila? Click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Hummel Guy" wrote:
I have a worksheet (S1) that I want to, from a separate sheet (S2) (same
workbook) search a colum in S1 and find each case where "P" occurs and return
all of the data from that "P" row. Furthermore, I want to omit the rows in
S2 where the "P" does not occur so that it will generate a compact report.
Any thoughts?

Welcome. Could you spare a moment to click the YES button in that response?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Hummel Guy" wrote:
Awesome reply. You nailed it and I apreciate your help!