Queries/Extracting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bec - 18 Jan 2008 00:23 GMT

I've been going through past Qs without a solution

I have a spreadsheet with multiple columns and rows.
I need multiple criteria to be met (all true) and then have the whole row
extracted to a new worksheet, whilst ignoring blank entries. Below is an
example of the .xls.

Status    Request    Date    M OR B    Site    PC    17"    19"    24"    A4
OPEN    376929    11-Dec-07    M    ORH
OPEN    404577    1-Feb-07    M    RIC                5
CLOSED    439579    22-Mar-07    M    LMA
CLOSED    448897    12-Apr-07    M    ALB
CLOSED    457180    27-Apr-07    M    RIC
OPEN    466290    14-May-07    B    LMA
OPEN    466291    14-May-07    B    LMA
CLOSED    466416    15-May-07    M    ALB    3    3            1
CLOSED    469083    21-May-07    M    RIC
OPEN    469596    21-May-07    M    ORH    1            1
OPEN    470477    22-May-07    M    ALB    1            1
OPEN    470583    23-May-07    M    RIC
CLOSED    471075    23-May-07    M    ORH

Reply to this Message

FSt1 - 18 Jan 2008 01:03 GMT

hi
is this a test where we have to guess what the multiple criteria is?

regards
FSt1

> I've been going through past Qs without a solution
>
[quoted text clipped - 17 lines]
> OPEN 470583 23-May-07 M RIC
> CLOSED 471075 23-May-07 M ORH

Reply to this Message

Pete_UK - 18 Jan 2008 01:39 GMT

Two of my earlier responses have got lost, so I'll try again - use
autofilters so that you can set up your multiple criteria, then highlight
the visible rows and copy/paste them to a new worksheet.

Hope this helps.

Pete

> I've been going through past Qs without a solution
>
[quoted text clipped - 17 lines]
> OPEN 470583 23-May-07 M RIC
> CLOSED 471075 23-May-07 M ORH

Reply to this Message

Bec - 18 Jan 2008 02:08 GMT

Yes it does in a way. I'm looking to try an automate as the I'm dealing with
over 20 columns and 300 rows. I'd like to set up something a bit more
permanant that updates automatically by extracting new data as the
spreadsheet is updated.

A typical criteria would be
open, by site, by m or b, by 17'

Bec

> Two of my earlier responses have got lost, so I'll try again - use
> autofilters so that you can set up your multiple criteria, then highlight
[quoted text clipped - 25 lines]
> > OPEN 470583 23-May-07 M RIC
> > CLOSED 471075 23-May-07 M ORH

Reply to this Message

Pete_UK - 18 Jan 2008 09:24 GMT

Try to record a macro while you do this once, then you can play the
macro back each time you want to apply it.

Pete

> Yes it does in a way. I'm looking to try an automate as the I'm dealing with
> over 20 columns and 300 rows. I'd like to set up something a bit more
[quoted text clipped - 37 lines]
>
> - Show quoted text -

Reply to this Message