Remember Me?

Menu

#1
Posted to microsoft.public.excel.worksheet.functions
 steve_sr2 Posts: n/a
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
 Excel Super Guru Posts: 1,867
Answer: return multiple rows of data based on criteria

1. To return multiple rows of data based on criteria with multiple criteria, you can use the combination of the INDEX, MATCH, and IF functions. Here are the steps:
2. In Sheet2, set up the criteria range. In your example, this would be the date range of 3/1/06 and 3/15/06.
3. In the first cell where you want to display the matching data, enter the following formula:

Formula:
``` =IFERROR(INDEX(Sheet1!\$A\$2:\$I\$4,SMALL(IF((Sheet1!\$A\$2:\$A\$4=\$F\$5)*(Sheet1!\$C\$2:\$C\$4=\$G\$5)*(Sheet1!\$C\$2:\$C\$4<=\$H\$5),ROW(Sheet1!\$A\$2:\$A\$4)-1),ROW(1:1)),1),"")  ```
Note: This formula assumes that your data in Sheet1 starts at row 2 and that your criteria range is in cells F5, G5, and H5. Adjust the ranges accordingly.
4. Press Ctrl+Shift+Enter to enter the formula as an array formula. The formula should now display the first matching row of data.
5. Copy the formula down as many rows as you need to display all the matching data.

The formula works by using the IF function to check if each row in Sheet1 meets all the criteria. The criteria a

- Column A matches the value in cell F5
- Column C is greater than or equal to the value in cell G5
- Column C is less than or equal to the value in cell H5

If a row meets all the criteria, the IF function returns the row number minus 1 (to adjust for the fact that the data starts in row 2). The SMALL function then returns the nth smallest row number, where n is the row number of the formula (1 for the first row, 2 for the second row, etc.). Finally, the INDEX function returns the value in the first column of the matching row.

The IFERROR function is used to display a blank cell if there are no more matching rows.
__________________
I am not human. I am an Excel Wizard
#3
Posted to microsoft.public.excel.worksheet.functions
 Biff Posts: n/a
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)),CO LUMNS(\$A:A))

Copy across then down.

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

Biff

"steve_sr2" wrote in message
...
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
Posted to microsoft.public.excel.worksheet.functions
 via135 Posts: n/a
return multiple rows of data based on criteria

hi!

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

-via135

steve_sr2 Wrote:
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!

--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=516454

#5
Posted to microsoft.public.excel.worksheet.functions
 Arvi Laanemets Posts: n/a
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
your criterias on Sheet2. I.e. with criterias in your example, your table
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" wrote in message
...
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
Posted to microsoft.public.excel.worksheet.functions
 steve_sr2 Posts: n/a
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)),CO LUMNS(\$A:A))

Copy across then down.

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

Biff

"steve_sr2" wrote in message
...
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
Posted to microsoft.public.excel.worksheet.functions
 Domenic Posts: n/a
return multiple rows of data based on criteria

Yes...

In article ,
steve_sr2 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)),CO LUMNS(\$A:A))

Copy across then down.

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

Biff

"steve_sr2" wrote in message
...
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
Posted to microsoft.public.excel.worksheet.functions
 steve_sr2 Posts: n/a
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 ,
steve_sr2 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)),CO LUMNS(\$A:A))

Copy across then down.

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

Biff

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

#9
Posted to microsoft.public.excel.worksheet.functions
 Biff Posts: n/a
return multiple rows of data based on criteria

Hi!

Just add another array:

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)),CO LUMNS(\$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" wrote in message
...
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 ,
steve_sr2 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)),CO LUMNS(\$A:A))

Copy across then down.

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

Biff

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

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post [email protected] Excel Worksheet Functions 0 December 1st 05 01:56 PM Joe D Excel Worksheet Functions 4 November 20th 05 11:51 PM Sam via OfficeKB.com Excel Worksheet Functions 4 October 13th 05 04:22 AM baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM Murph Excel Worksheet Functions 4 February 21st 05 02:44 AM

All times are GMT +1. The time now is 09:11 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright ©2004-2023 ExcelBanter.
The comments are property of their posters.

About Us

"It's about Microsoft Excel"