Extracting Data
 

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

Extracting Data
 

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


"leimst" wrote:

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

Extracting Data
 

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

Regards,
Fred

"Teethless mama" wrote in message
...
=SUMPRODUCT((C1:C100="National")*(G1:G100="Account ")*(LEFT(L1:L100,4)="Acme))


"leimst" wrote:

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

Extracting Data
 

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" wrote in message
...
Correcting typo:
=SUMPRODUCT((C1:C100="National")*(G1:G100="Account ")*(LEFT(L1:L100,4)="Acme"))

Regards,
Fred

"Teethless mama" wrote in
message ...
=SUMPRODUCT((C1:C100="National")*(G1:G100="Account ")*(LEFT(L1:L100,4)="Acme))


"leimst" wrote:

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

Extracting Data
 

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.

"leimst" wrote in message
...
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" wrote in message
...
Correcting typo:
=SUMPRODUCT((C1:C100="National")*(G1:G100="Account ")*(LEFT(L1:L100,4)="Acme"))

Regards,
Fred

"Teethless mama" wrote in
message ...
=SUMPRODUCT((C1:C100="National")*(G1:G100="Account ")*(LEFT(L1:L100,4)="Acme))


"leimst" wrote:

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

Extracting Data
 

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

"leimst" wrote in message
...
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