# [SOLVED] return multiple rows of data based on criteria

1. ## [SOLVED] return multiple rows of data based on criteria

I have two sheets. Sheet1 has multiple lines of data. Based on criteria
chosen in sheet2, I need to display in sheet2 all of the rows of data that
match the criteria. I'm having trouble with multiple criteria (i.e. for
name1, I want all matching rows of data in a certain timeframe. Not all data
in ColA is are same name and will not be in any particular order.

Sheet1
A B C D E F G H I
Bryan D Smith 2/1/06 1 2 2 6 6 1
Bryan D Jones 3/1/06 2 1 3 4 2 1
Bryan D Smith 3/5/06 1 1 1 1 1 1

Sheet 2 would return based on user selecting between dates of 3/1/06 & 3/15/06
Bryan D Jones 3/1/06 2 1 3 4 2 1
Bryan D Smith 3/5/06 1 1 1 1 1 1

I can get it to return all of the items for that match in column A but how
do I for certain dates?

Here is my formula that I'm using to get all rows that match

{=INDEX('data entry'!\$C\$3:\$U\$34,SMALL(IF('data
entry'!\$C\$3:\$C\$34=\$F\$5,ROW(\$1:\$32)),ROW(1:1)),3)}

Thanks in advance for any help!

2. ## Re: return multiple rows of data based on criteria

Hi!

One way:

Use 2 cells to hold the date criteria:

A1 = 3/1/06
B1 = 3/15/06

Array entered:

=INDEX('data entry'!\$A\$3:\$U\$34,SMALL(IF(('data
entry'!\$C\$3:\$C\$34>=\$A\$1)*('data
entry'!\$C\$3:\$C\$34<=\$B\$1),ROW(\$1:\$32)),ROW(1:1)),COLUMNS(\$A:A))

Copy across then down.

It'll start extracting from column A (name).

Biff

"steve_sr2" <stevesr2@discussions.microsoft.com> wrote in message
news:D3C1D8CB-5005-41EE-90B4-A9816F004D02@microsoft.com...
>I have two sheets. Sheet1 has multiple lines of data. Based on criteria
> chosen in sheet2, I need to display in sheet2 all of the rows of data that
> match the criteria. I'm having trouble with multiple criteria (i.e. for
> name1, I want all matching rows of data in a certain timeframe. Not all
> data
> in ColA is are same name and will not be in any particular order.
>
> Sheet1
> A B C D E F G H I
> Bryan D Smith 2/1/06 1 2 2 6 6 1
> Bryan D Jones 3/1/06 2 1 3 4 2 1
> Bryan D Smith 3/5/06 1 1 1 1 1 1
>
> Sheet 2 would return based on user selecting between dates of 3/1/06 &
> 3/15/06
> Bryan D Jones 3/1/06 2 1 3 4 2 1
> Bryan D Smith 3/5/06 1 1 1 1 1 1
>
> I can get it to return all of the items for that match in column A but how
> do I for certain dates?
>
> Here is my formula that I'm using to get all rows that match
>
> {=INDEX('data entry'!\$C\$3:\$U\$34,SMALL(IF('data
> entry'!\$C\$3:\$C\$34=\$F\$5,ROW(\$1:\$32)),ROW(1:1)),3)}
>
> Thanks in advance for any help!

3. ## return multiple rows of data based on criteria!

hi!

i think an "advance filter" will suffice!
have you tired that?

-via135

Originally Posted by steve_sr2
I have two sheets. Sheet1 has multiple lines of data. Based on criteria
chosen in sheet2, I need to display in sheet2 all of the rows of data that
match the criteria. I'm having trouble with multiple criteria (i.e. for
name1, I want all matching rows of data in a certain timeframe. Not all data
in ColA is are same name and will not be in any particular order.

Sheet1
A B C D E F G H I
Bryan D Smith 2/1/06 1 2 2 6 6 1
Bryan D Jones 3/1/06 2 1 3 4 2 1
Bryan D Smith 3/5/06 1 1 1 1 1 1

Sheet 2 would return based on user selecting between dates of 3/1/06 & 3/15/06
Bryan D Jones 3/1/06 2 1 3 4 2 1
Bryan D Smith 3/5/06 1 1 1 1 1 1

I can get it to return all of the items for that match in column A but how
do I for certain dates?

Here is my formula that I'm using to get all rows that match

{=INDEX('data entry'!\$C\$3:\$U\$34,SMALL(IF('data
entry'!\$C\$3:\$C\$34=\$F\$5,ROW(\$1:\$32)),ROW(1:1)),3)}

Thanks in advance for any help!

4. ## Re: return multiple rows of data based on criteria

Hi

Anoher way:
On Sheet1, add a (hidden) column as leftmost (column A) into your table.
Into this column, enter some formula, which numbers all rows, matching all
will look like
Bryan D Smith 2/1/06 1 2 2 6 6 1
1 Bryan D Jones 3/1/06 2 1 3 4 2 1
2 Bryan D Smith 3/5/06 1 1 1 1 1 1

On Sheet2, use VLOOKUP to get matching rows from Sheet1 - i.e. in 1st row of
return table is displayed data from Sheet1 with 1 in column A, in next row
with 2, etc.

Arvi Laanemets

"steve_sr2" <stevesr2@discussions.microsoft.com> wrote in message
news:D3C1D8CB-5005-41EE-90B4-A9816F004D02@microsoft.com...
> I have two sheets. Sheet1 has multiple lines of data. Based on criteria
> chosen in sheet2, I need to display in sheet2 all of the rows of data that
> match the criteria. I'm having trouble with multiple criteria (i.e. for
> name1, I want all matching rows of data in a certain timeframe. Not all

data
> in ColA is are same name and will not be in any particular order.
>
> Sheet1
> A B C D E F G H I
> Bryan D Smith 2/1/06 1 2 2 6 6 1
> Bryan D Jones 3/1/06 2 1 3 4 2 1
> Bryan D Smith 3/5/06 1 1 1 1 1 1
>
> Sheet 2 would return based on user selecting between dates of 3/1/06 &

3/15/06
> Bryan D Jones 3/1/06 2 1 3 4 2 1
> Bryan D Smith 3/5/06 1 1 1 1 1 1
>
> I can get it to return all of the items for that match in column A but how
> do I for certain dates?
>
> Here is my formula that I'm using to get all rows that match
>
> {=INDEX('data entry'!\$C\$3:\$U\$34,SMALL(IF('data
> entry'!\$C\$3:\$C\$34=\$F\$5,ROW(\$1:\$32)),ROW(1:1)),3)}
>
> Thanks in advance for any help!

5. ## Re: return multiple rows of data based on criteria

Thanks for the help....Should this be entered with Ctr+Shift+Enter?

"Biff" wrote:

> Hi!
>
> One way:
>
> Use 2 cells to hold the date criteria:
>
> A1 = 3/1/06
> B1 = 3/15/06
>
> Array entered:
>
> =INDEX('data entry'!\$A\$3:\$U\$34,SMALL(IF(('data
> entry'!\$C\$3:\$C\$34>=\$A\$1)*('data
> entry'!\$C\$3:\$C\$34<=\$B\$1),ROW(\$1:\$32)),ROW(1:1)),COLUMNS(\$A:A))
>
> Copy across then down.
>
> It'll start extracting from column A (name).
>
> Biff
>
> "steve_sr2" <stevesr2@discussions.microsoft.com> wrote in message
> news:D3C1D8CB-5005-41EE-90B4-A9816F004D02@microsoft.com...
> >I have two sheets. Sheet1 has multiple lines of data. Based on criteria
> > chosen in sheet2, I need to display in sheet2 all of the rows of data that
> > match the criteria. I'm having trouble with multiple criteria (i.e. for
> > name1, I want all matching rows of data in a certain timeframe. Not all
> > data
> > in ColA is are same name and will not be in any particular order.
> >
> > Sheet1
> > A B C D E F G H I
> > Bryan D Smith 2/1/06 1 2 2 6 6 1
> > Bryan D Jones 3/1/06 2 1 3 4 2 1
> > Bryan D Smith 3/5/06 1 1 1 1 1 1
> >
> > Sheet 2 would return based on user selecting between dates of 3/1/06 &
> > 3/15/06
> > Bryan D Jones 3/1/06 2 1 3 4 2 1
> > Bryan D Smith 3/5/06 1 1 1 1 1 1
> >
> > I can get it to return all of the items for that match in column A but how
> > do I for certain dates?
> >
> > Here is my formula that I'm using to get all rows that match
> >
> > {=INDEX('data entry'!\$C\$3:\$U\$34,SMALL(IF('data
> > entry'!\$C\$3:\$C\$34=\$F\$5,ROW(\$1:\$32)),ROW(1:1)),3)}
> >
> > Thanks in advance for any help!

>
>
>

6. ## Re: return multiple rows of data based on criteria

Yes...

In article <BD571931-7D69-48D6-8C4C-353BC7D0A639@microsoft.com>,
steve_sr2 <stevesr2@discussions.microsoft.com> wrote:

> Thanks for the help....Should this be entered with Ctr+Shift+Enter?
>
> "Biff" wrote:
>
> > Hi!
> >
> > One way:
> >
> > Use 2 cells to hold the date criteria:
> >
> > A1 = 3/1/06
> > B1 = 3/15/06
> >
> > Array entered:
> >
> > =INDEX('data entry'!\$A\$3:\$U\$34,SMALL(IF(('data
> > entry'!\$C\$3:\$C\$34>=\$A\$1)*('data
> > entry'!\$C\$3:\$C\$34<=\$B\$1),ROW(\$1:\$32)),ROW(1:1)),COLUMNS(\$A:A))
> >
> > Copy across then down.
> >
> > It'll start extracting from column A (name).
> >
> > Biff
> >
> > "steve_sr2" <stevesr2@discussions.microsoft.com> wrote in message
> > news:D3C1D8CB-5005-41EE-90B4-A9816F004D02@microsoft.com...
> > >I have two sheets. Sheet1 has multiple lines of data. Based on criteria
> > > chosen in sheet2, I need to display in sheet2 all of the rows of data that
> > > match the criteria. I'm having trouble with multiple criteria (i.e. for
> > > name1, I want all matching rows of data in a certain timeframe. Not all
> > > data
> > > in ColA is are same name and will not be in any particular order.
> > >
> > > Sheet1
> > > A B C D E F G H I
> > > Bryan D Smith 2/1/06 1 2 2 6 6 1
> > > Bryan D Jones 3/1/06 2 1 3 4 2 1
> > > Bryan D Smith 3/5/06 1 1 1 1 1 1
> > >
> > > Sheet 2 would return based on user selecting between dates of 3/1/06 &
> > > 3/15/06
> > > Bryan D Jones 3/1/06 2 1 3 4 2 1
> > > Bryan D Smith 3/5/06 1 1 1 1 1 1
> > >
> > > I can get it to return all of the items for that match in column A but how
> > > do I for certain dates?
> > >
> > > Here is my formula that I'm using to get all rows that match
> > >
> > > {=INDEX('data entry'!\$C\$3:\$U\$34,SMALL(IF('data
> > > entry'!\$C\$3:\$C\$34=\$F\$5,ROW(\$1:\$32)),ROW(1:1)),3)}
> > >
> > > Thanks in advance for any help!

> >
> >
> >

7. ## Re: return multiple rows of data based on criteria

That worked great. Thx. Couple of more questions. If I were to add an
additional element into the equation, for instance, employee name in between
certain dates, how would the formula look. Also, What does the asterisk in
the formula do?

"Domenic" wrote:

> Yes...
>
> In article <BD571931-7D69-48D6-8C4C-353BC7D0A639@microsoft.com>,
> steve_sr2 <stevesr2@discussions.microsoft.com> wrote:
>
> > Thanks for the help....Should this be entered with Ctr+Shift+Enter?
> >
> > "Biff" wrote:
> >
> > > Hi!
> > >
> > > One way:
> > >
> > > Use 2 cells to hold the date criteria:
> > >
> > > A1 = 3/1/06
> > > B1 = 3/15/06
> > >
> > > Array entered:
> > >
> > > =INDEX('data entry'!\$A\$3:\$U\$34,SMALL(IF(('data
> > > entry'!\$C\$3:\$C\$34>=\$A\$1)*('data
> > > entry'!\$C\$3:\$C\$34<=\$B\$1),ROW(\$1:\$32)),ROW(1:1)),COLUMNS(\$A:A))
> > >
> > > Copy across then down.
> > >
> > > It'll start extracting from column A (name).
> > >
> > > Biff
> > >
> > > "steve_sr2" <stevesr2@discussions.microsoft.com> wrote in message
> > > news:D3C1D8CB-5005-41EE-90B4-A9816F004D02@microsoft.com...
> > > >I have two sheets. Sheet1 has multiple lines of data. Based on criteria
> > > > chosen in sheet2, I need to display in sheet2 all of the rows of data that
> > > > match the criteria. I'm having trouble with multiple criteria (i.e. for
> > > > name1, I want all matching rows of data in a certain timeframe. Not all
> > > > data
> > > > in ColA is are same name and will not be in any particular order.
> > > >
> > > > Sheet1
> > > > A B C D E F G H I
> > > > Bryan D Smith 2/1/06 1 2 2 6 6 1
> > > > Bryan D Jones 3/1/06 2 1 3 4 2 1
> > > > Bryan D Smith 3/5/06 1 1 1 1 1 1
> > > >
> > > > Sheet 2 would return based on user selecting between dates of 3/1/06 &
> > > > 3/15/06
> > > > Bryan D Jones 3/1/06 2 1 3 4 2 1
> > > > Bryan D Smith 3/5/06 1 1 1 1 1 1
> > > >
> > > > I can get it to return all of the items for that match in column A but how
> > > > do I for certain dates?
> > > >
> > > > Here is my formula that I'm using to get all rows that match
> > > >
> > > > {=INDEX('data entry'!\$C\$3:\$U\$34,SMALL(IF('data
> > > > entry'!\$C\$3:\$C\$34=\$F\$5,ROW(\$1:\$32)),ROW(1:1)),3)}
> > > >
> > > > Thanks in advance for any help!
> > >
> > >
> > >

>

8. ## Re: return multiple rows of data based on criteria

Hi!

A1 = 3/1/06
B1 = 3/15/06
C1 = some name

Assuming th names are in column A.

=INDEX('data entry'!\$A\$3:\$U\$34,SMALL(IF(('data
entry'!\$A\$3:\$A\$34=\$C\$1)*('data entry'!\$C\$3:\$C\$34>=\$A\$1)*('data
entry'!\$C\$3:\$C\$34<=\$B\$1),ROW(\$1:\$32)),ROW(1:1)),COLUMNS(\$A:A))

>What does the asterisk in the formula do?

It's the multiplication operator. The (now) 3 arrays are multiplied
together:

(A3:A34=C1)*(C3:C34>=A1)*(C3:C34<=B1)

For a deluxe explanation of how this formula works, see this

http://tinyurl.com/njzjh

Biff

"steve_sr2" <stevesr2@discussions.microsoft.com> wrote in message
news:36142A9B-8B29-4033-89A9-50CA6B5D9A97@microsoft.com...
> That worked great. Thx. Couple of more questions. If I were to add an
> additional element into the equation, for instance, employee name in
> between
> certain dates, how would the formula look. Also, What does the asterisk
> in
> the formula do?
>
> "Domenic" wrote:
>
>> Yes...
>>
>> In article <BD571931-7D69-48D6-8C4C-353BC7D0A639@microsoft.com>,
>> steve_sr2 <stevesr2@discussions.microsoft.com> wrote:
>>
>> > Thanks for the help....Should this be entered with Ctr+Shift+Enter?
>> >
>> > "Biff" wrote:
>> >
>> > > Hi!
>> > >
>> > > One way:
>> > >
>> > > Use 2 cells to hold the date criteria:
>> > >
>> > > A1 = 3/1/06
>> > > B1 = 3/15/06
>> > >
>> > > Array entered:
>> > >
>> > > =INDEX('data entry'!\$A\$3:\$U\$34,SMALL(IF(('data
>> > > entry'!\$C\$3:\$C\$34>=\$A\$1)*('data
>> > > entry'!\$C\$3:\$C\$34<=\$B\$1),ROW(\$1:\$32)),ROW(1:1)),COLUMNS(\$A:A))
>> > >
>> > > Copy across then down.
>> > >
>> > > It'll start extracting from column A (name).
>> > >
>> > > Biff
>> > >
>> > > "steve_sr2" <stevesr2@discussions.microsoft.com> wrote in message
>> > > news:D3C1D8CB-5005-41EE-90B4-A9816F004D02@microsoft.com...
>> > > >I have two sheets. Sheet1 has multiple lines of data. Based on
>> > > >criteria
>> > > > chosen in sheet2, I need to display in sheet2 all of the rows of
>> > > > data that
>> > > > match the criteria. I'm having trouble with multiple criteria
>> > > > (i.e. for
>> > > > name1, I want all matching rows of data in a certain timeframe.
>> > > > Not all
>> > > > data
>> > > > in ColA is are same name and will not be in any particular order.
>> > > >
>> > > > Sheet1
>> > > > A B C D E F G H I
>> > > > Bryan D Smith 2/1/06 1 2 2 6 6 1
>> > > > Bryan D Jones 3/1/06 2 1 3 4 2 1
>> > > > Bryan D Smith 3/5/06 1 1 1 1 1 1
>> > > >
>> > > > Sheet 2 would return based on user selecting between dates of
>> > > > 3/1/06 &
>> > > > 3/15/06
>> > > > Bryan D Jones 3/1/06 2 1 3 4 2 1
>> > > > Bryan D Smith 3/5/06 1 1 1 1 1 1
>> > > >
>> > > > I can get it to return all of the items for that match in column A
>> > > > but how
>> > > > do I for certain dates?
>> > > >
>> > > > Here is my formula that I'm using to get all rows that match
>> > > >
>> > > > {=INDEX('data entry'!\$C\$3:\$U\$34,SMALL(IF('data
>> > > > entry'!\$C\$3:\$C\$34=\$F\$5,ROW(\$1:\$32)),ROW(1:1)),3)}
>> > > >
>> > > > Thanks in advance for any help!
>> > >
>> > >
>> > >

>>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1