Extract data that meets specific condition from large raw data

[tekobayashi]

I have a sheet (Raw Data) with huge database (minimum 500 rows). On the "Output" sheet, I would like to show all the data that meets specific criteria.

On the "Output" sheet, I want to show all data that (1) "Sales Rep" (Column H of "Raw Data") is equal to name specified on "Output" (B3) and (2) "Comp. Date" (Column L of "Raw Data") is between the dates specified on "Output" (B1 and B2).

The sample file I uploaded only has 49 lines of data, but actually it could go up to 500 lines. Therefore, the number of data lines that will be shown on "Output" Sheet will vary depending on the raw data, the specified salesperson name and specified dates.

Also, I reduced the number of columns in the raw data for this sample file, but the actual file has about 40 columns of data fields.

I've been trying my best with INDEX, MATCH, SMALL, LARGE, etc. but had no luck. I would appreciate any sort of help on this.

Thank you in advance.

[FDibbins]

without looking at you're file, have you tried using filters, or maybe pivot tables?

[tekobayashi]

I need it to be done automatically, because this is supposed to be a report that is generated automatically when the user copy/pastes the raw data onto the Raw Data sheet.

[:) Sixthsense :)]

In A6 cell of Output Sheet - Array Formula Requires Ctrl+Shift+Enter

Please Login or Register  to view this content.

Drag it down and right.

Keep both RawData and Output sheet Column Headers as same.

[tekobayashi]

Thank you! Actually, there was a minor mistake in the formula, but I figured it out.

=IFERROR(INDEX('Raw Data'!$A$1:$O$50,SMALL(IF(('Raw Data'!$H$1:$H$50=$B$3)*($B$1>='Raw Data'!$I$1:$I$50)*('Raw Data'!$I$1:$I$50<=$B$2),ROW('Raw Data'!$I$1:$I$50)),ROW($A1)),MATCH(Output!A$5,'Raw Data'!$A$1:$O$1,0)),"")

Should be 'Raw Data'!$I$1:$I$50 >= $B$1 instead of $B$1>='Raw Data'!$I$1:$I$50. But neverthless, it worked like magic. Thanks!

[:) Sixthsense :)]

Thanks for the feedback and correction!

Glad to know that you got the solution