Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.worksheet.functions




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




Answer: return multiple rows of data based on criteria
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.worksheet.functions




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




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




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




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




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




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




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! 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Excel Macro to Copy & Paste  Excel Worksheet Functions  
Show top five records based on meeting multiple criteria  Excel Worksheet Functions  
Match Each Numeric occurrence and Return Individual Rows of Data  Excel Worksheet Functions  
Help PLEASE! Not sure what answer is: Match? Index? Other?  Excel Worksheet Functions  
Formula to compare multiple rows values based on another column?  Excel Worksheet Functions 