Extracting Data

[leimst]

I have a large spreadsheet and I would like to search 3 of the colums for
certain information. If any of the information is found in any of the 3
columns, I would like to copy and paste that entire row to a new worksheet.
Let's say that I would like to search column C for "National", column G for
"Account" and column L for any entry starting with "Acme" (so maybe
"Acme*"?).

Any help would be appreciated.

Brian

 

[Teethless mama]

=SUMPRODUCT((C1:C100="National")*(G1:G100="Account")*(LEFT(L1:L100,4)="Acme))

 

[Fred Smith]

Correcting typo:
=SUMPRODUCT((C1:C100="National")*(G1:G100="Account")*(LEFT(L1:L100,4)="Acme"))

Regards,
Fred

 

[leimst]

I haven't used this function before. Can I place it anywhere in the
spreadsheet? Right now it's returning a "0"...maybe I have something wrong?

Thanks, Brian

 

[Fred Smith]

Yes, you can place it anywhere in the spreadsheet.

Did you copy it, or did you type it in? If you typed it in, did you check
for errors?
Did you adjust the ranges to be appropriate for your setup?

The formula works for me, so it's either your data or a copying error.

Regards,
Fred.

 

[Ashish Mathur]

Hi,

This is what Advanced filters are used for. Please read up on advanced
filters in the Help menu

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com