|
Getting all rows of data that have a value in a particular column
Hi. Is there a way to list, on a separate sheet, only rows of data with a
value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular column
Is there a way to do this with a formula and not a pivot table?
Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Thanks Biff. Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Would it still be practical if I had 250 rows of data total and
needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular column
Here's what I would do.
I would copy my 10 columns then paste it into the other worksheet (paste as link) then use a filter.....filter by values greater than 0 in column F. HTH Jean-Guy "Rikki-Handgards" wrote: Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Here's a link to a sample file:
http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Thank you very much Biff, the formula works exactly like I needed it to.
However, I am having problems entering the array formula. I have the normal formula in a cell and then I select the rows beneath it and hit F2 and press Ctrl+Shift+Enter but the formulas don't copy correctly. I've tried it several different ways, but I always get =IF(ROWS('Sheet1'!$1:1)... in every row instead of ....('Sheet1'!$1:2),...('Sheet1'!$1:3), etc. If I copy the formula down the column the formulas change like they are supposed to. Then, when I select them all and enter it as an array formula, they all change back to ('Sheet1'!$1:1) again. Can you tell me what I am doing wrong? Thank you. -Rikki "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Hi!
Just enter the formula in one cell, the top left cell of the "grid", and enter it as an array using the key combo of CTRL,SHIFT,ENTER. With the formula typed into that one top left cell move the cursor (mouse) to the end of the formula in the formula bar. Then enter it as an array. Then, just drag copy across the row to the right. Then drag copy down the columns as needed. Each cell formula is an array. You don't want to make the entire grid a single block array. Biff "Rikki-Handgards" wrote in message ... Thank you very much Biff, the formula works exactly like I needed it to. However, I am having problems entering the array formula. I have the normal formula in a cell and then I select the rows beneath it and hit F2 and press Ctrl+Shift+Enter but the formulas don't copy correctly. I've tried it several different ways, but I always get =IF(ROWS('Sheet1'!$1:1)... in every row instead of ...('Sheet1'!$1:2),...('Sheet1'!$1:3), etc. If I copy the formula down the column the formulas change like they are supposed to. Then, when I select them all and enter it as an array formula, they all change back to ('Sheet1'!$1:1) again. Can you tell me what I am doing wrong? Thank you. -Rikki "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Hello. Thanks so much, it worked! of course :) I appreciate your time Biff.
"Biff" wrote: Hi! Just enter the formula in one cell, the top left cell of the "grid", and enter it as an array using the key combo of CTRL,SHIFT,ENTER. With the formula typed into that one top left cell move the cursor (mouse) to the end of the formula in the formula bar. Then enter it as an array. Then, just drag copy across the row to the right. Then drag copy down the columns as needed. Each cell formula is an array. You don't want to make the entire grid a single block array. Biff "Rikki-Handgards" wrote in message ... Thank you very much Biff, the formula works exactly like I needed it to. However, I am having problems entering the array formula. I have the normal formula in a cell and then I select the rows beneath it and hit F2 and press Ctrl+Shift+Enter but the formulas don't copy correctly. I've tried it several different ways, but I always get =IF(ROWS('Sheet1'!$1:1)... in every row instead of ...('Sheet1'!$1:2),...('Sheet1'!$1:3), etc. If I copy the formula down the column the formulas change like they are supposed to. Then, when I select them all and enter it as an array formula, they all change back to ('Sheet1'!$1:1) again. Can you tell me what I am doing wrong? Thank you. -Rikki "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
I have a list 4 x 316:
county code sales tax smith 36-02 25.00 5.00 allen 14-17 14.00 1.40 caddo 72-36 .00 .00 park 55 -92 97.00 9.70 In a different part of the spread sheet I would like: code sales tax county 36-02 25.00 5.00 smith 14-17 14.00 1.40 allen 55-92 97.00 9.70 park I would like this list updated 'automatically' when something is changed in the top part. The numbers in the top are looked up on another spreadsheet and populated upon opening of the workbook. Do you need anything else? Thanks for your help "Biff" wrote: Hi! How about explaining exactly what you want to do (include the DETAILS!) and if I can figure it out I'll post a fresh link with a sample. Biff "Sunny" wrote in message ... This is exactly what I would liek to do! However, the link no longer works. Would you please send it again? Thanks- sunny "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Ok.....
So you want to extract data where there is a value greater than 0 in *BOTH* Sales and Tax? Is the size of the table always the same? Always 4c x 316r ? What is the cell address where the table starts? For example, based on you sample Smith appears in cell A2. The reason I "need" to know this is because the formula will contain an expression based on this starting cell. A lot of times a poster will copy the formula from a reply and change this expression incorrectly and that ends up causing the formula to not work properly leading to a bunch of follow-up questions. This should be no problem. Biff "Sunny" wrote in message ... I have a list 4 x 316: county code sales tax smith 36-02 25.00 5.00 allen 14-17 14.00 1.40 caddo 72-36 .00 .00 park 55 -92 97.00 9.70 In a different part of the spread sheet I would like: code sales tax county 36-02 25.00 5.00 smith 14-17 14.00 1.40 allen 55-92 97.00 9.70 park I would like this list updated 'automatically' when something is changed in the top part. The numbers in the top are looked up on another spreadsheet and populated upon opening of the workbook. Do you need anything else? Thanks for your help "Biff" wrote: Hi! How about explaining exactly what you want to do (include the DETAILS!) and if I can figure it out I'll post a fresh link with a sample. Biff "Sunny" wrote in message ... This is exactly what I would liek to do! However, the link no longer works. Would you please send it again? Thanks- sunny "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
316 is a constant number.
It starts in A4 and can land anyplace right of W. "Biff" wrote: Ok..... So you want to extract data where there is a value greater than 0 in *BOTH* Sales and Tax? Is the size of the table always the same? Always 4c x 316r ? What is the cell address where the table starts? For example, based on you sample Smith appears in cell A2. The reason I "need" to know this is because the formula will contain an expression based on this starting cell. A lot of times a poster will copy the formula from a reply and change this expression incorrectly and that ends up causing the formula to not work properly leading to a bunch of follow-up questions. This should be no problem. Biff "Sunny" wrote in message ... I have a list 4 x 316: county code sales tax smith 36-02 25.00 5.00 allen 14-17 14.00 1.40 caddo 72-36 .00 .00 park 55 -92 97.00 9.70 In a different part of the spread sheet I would like: code sales tax county 36-02 25.00 5.00 smith 14-17 14.00 1.40 allen 55-92 97.00 9.70 park I would like this list updated 'automatically' when something is changed in the top part. The numbers in the top are looked up on another spreadsheet and populated upon opening of the workbook. Do you need anything else? Thanks for your help "Biff" wrote: Hi! How about explaining exactly what you want to do (include the DETAILS!) and if I can figure it out I'll post a fresh link with a sample. Biff "Sunny" wrote in message ... This is exactly what I would liek to do! However, the link no longer works. Would you please send it again? Thanks- sunny "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
can land anyplace right of W
What does that mean? You want the extracted data to be placed anywhere to the right of column W? Biff "Sunny" wrote in message ... 316 is a constant number. It starts in A4 and can land anyplace right of W. "Biff" wrote: Ok..... So you want to extract data where there is a value greater than 0 in *BOTH* Sales and Tax? Is the size of the table always the same? Always 4c x 316r ? What is the cell address where the table starts? For example, based on you sample Smith appears in cell A2. The reason I "need" to know this is because the formula will contain an expression based on this starting cell. A lot of times a poster will copy the formula from a reply and change this expression incorrectly and that ends up causing the formula to not work properly leading to a bunch of follow-up questions. This should be no problem. Biff "Sunny" wrote in message ... I have a list 4 x 316: county code sales tax smith 36-02 25.00 5.00 allen 14-17 14.00 1.40 caddo 72-36 .00 .00 park 55 -92 97.00 9.70 In a different part of the spread sheet I would like: code sales tax county 36-02 25.00 5.00 smith 14-17 14.00 1.40 allen 55-92 97.00 9.70 park I would like this list updated 'automatically' when something is changed in the top part. The numbers in the top are looked up on another spreadsheet and populated upon opening of the workbook. Do you need anything else? Thanks for your help "Biff" wrote: Hi! How about explaining exactly what you want to do (include the DETAILS!) and if I can figure it out I'll post a fresh link with a sample. Biff "Sunny" wrote in message ... This is exactly what I would liek to do! However, the link no longer works. Would you please send it again? Thanks- sunny "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
yes and only the sales column needs to be checked for 0
"Biff" wrote: can land anyplace right of W What does that mean? You want the extracted data to be placed anywhere to the right of column W? Biff "Sunny" wrote in message ... 316 is a constant number. It starts in A4 and can land anyplace right of W. "Biff" wrote: Ok..... So you want to extract data where there is a value greater than 0 in *BOTH* Sales and Tax? Is the size of the table always the same? Always 4c x 316r ? What is the cell address where the table starts? For example, based on you sample Smith appears in cell A2. The reason I "need" to know this is because the formula will contain an expression based on this starting cell. A lot of times a poster will copy the formula from a reply and change this expression incorrectly and that ends up causing the formula to not work properly leading to a bunch of follow-up questions. This should be no problem. Biff "Sunny" wrote in message ... I have a list 4 x 316: county code sales tax smith 36-02 25.00 5.00 allen 14-17 14.00 1.40 caddo 72-36 .00 .00 park 55 -92 97.00 9.70 In a different part of the spread sheet I would like: code sales tax county 36-02 25.00 5.00 smith 14-17 14.00 1.40 allen 55-92 97.00 9.70 park I would like this list updated 'automatically' when something is changed in the top part. The numbers in the top are looked up on another spreadsheet and populated upon opening of the workbook. Do you need anything else? Thanks for your help "Biff" wrote: Hi! How about explaining exactly what you want to do (include the DETAILS!) and if I can figure it out I'll post a fresh link with a sample. Biff "Sunny" wrote in message ... This is exactly what I would liek to do! However, the link no longer works. Would you please send it again? Thanks- sunny "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Ok, no problem!
I'll be away from the computer for a few hours so check back later. Biff "Sunny" wrote in message ... yes and only the sales column needs to be checked for 0 "Biff" wrote: can land anyplace right of W What does that mean? You want the extracted data to be placed anywhere to the right of column W? Biff "Sunny" wrote in message ... 316 is a constant number. It starts in A4 and can land anyplace right of W. "Biff" wrote: Ok..... So you want to extract data where there is a value greater than 0 in *BOTH* Sales and Tax? Is the size of the table always the same? Always 4c x 316r ? What is the cell address where the table starts? For example, based on you sample Smith appears in cell A2. The reason I "need" to know this is because the formula will contain an expression based on this starting cell. A lot of times a poster will copy the formula from a reply and change this expression incorrectly and that ends up causing the formula to not work properly leading to a bunch of follow-up questions. This should be no problem. Biff "Sunny" wrote in message ... I have a list 4 x 316: county code sales tax smith 36-02 25.00 5.00 allen 14-17 14.00 1.40 caddo 72-36 .00 .00 park 55 -92 97.00 9.70 In a different part of the spread sheet I would like: code sales tax county 36-02 25.00 5.00 smith 14-17 14.00 1.40 allen 55-92 97.00 9.70 park I would like this list updated 'automatically' when something is changed in the top part. The numbers in the top are looked up on another spreadsheet and populated upon opening of the workbook. Do you need anything else? Thanks for your help "Biff" wrote: Hi! How about explaining exactly what you want to do (include the DETAILS!) and if I can figure it out I'll post a fresh link with a sample. Biff "Sunny" wrote in message ... This is exactly what I would liek to do! However, the link no longer works. Would you please send it again? Thanks- sunny "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
thanks!
"Biff" wrote: Ok, no problem! I'll be away from the computer for a few hours so check back later. Biff "Sunny" wrote in message ... yes and only the sales column needs to be checked for 0 "Biff" wrote: can land anyplace right of W What does that mean? You want the extracted data to be placed anywhere to the right of column W? Biff "Sunny" wrote in message ... 316 is a constant number. It starts in A4 and can land anyplace right of W. "Biff" wrote: Ok..... So you want to extract data where there is a value greater than 0 in *BOTH* Sales and Tax? Is the size of the table always the same? Always 4c x 316r ? What is the cell address where the table starts? For example, based on you sample Smith appears in cell A2. The reason I "need" to know this is because the formula will contain an expression based on this starting cell. A lot of times a poster will copy the formula from a reply and change this expression incorrectly and that ends up causing the formula to not work properly leading to a bunch of follow-up questions. This should be no problem. Biff "Sunny" wrote in message ... I have a list 4 x 316: county code sales tax smith 36-02 25.00 5.00 allen 14-17 14.00 1.40 caddo 72-36 .00 .00 park 55 -92 97.00 9.70 In a different part of the spread sheet I would like: code sales tax county 36-02 25.00 5.00 smith 14-17 14.00 1.40 allen 55-92 97.00 9.70 park I would like this list updated 'automatically' when something is changed in the top part. The numbers in the top are looked up on another spreadsheet and populated upon opening of the workbook. Do you need anything else? Thanks for your help "Biff" wrote: Hi! How about explaining exactly what you want to do (include the DETAILS!) and if I can figure it out I'll post a fresh link with a sample. Biff "Sunny" wrote in message ... This is exactly what I would liek to do! However, the link no longer works. Would you please send it again? Thanks- sunny "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Ok, here you go:
Sample file: Sample extract.xls 18.5kb http://cjoint.com/?ipe25QHXQb I took a little bit of a different approach on this one. I saw your other post!!!!! If you have 50 sheets and they average 300 rows one array formula copied to that many cells would really slow things down! So, I used just one array formula in column X and then simple Vlookups in columns Y and Z and then a simple Index/Match in column AA. Also, another reason for the different formula is that you want the extracted data in a slightly different sequence. I'm assuming that the "code" is unique to the county? That's what I based the lookup formulas on. So, all you should have to do is change the references to the actual end of range of your data. You'll have to copy the formulas down to enough rows so that they pick up all the related data. Typically, how many entries will have 0 sales? 10? 20? 200? That's how many rows you would need to copy the formulas to. Are you familar with array formulas? How to enter an array formula: http://cpearson.com/excel/array.htm Biff "Sunny" wrote in message ... thanks! "Biff" wrote: Ok, no problem! I'll be away from the computer for a few hours so check back later. Biff "Sunny" wrote in message ... yes and only the sales column needs to be checked for 0 "Biff" wrote: can land anyplace right of W What does that mean? You want the extracted data to be placed anywhere to the right of column W? Biff "Sunny" wrote in message ... 316 is a constant number. It starts in A4 and can land anyplace right of W. "Biff" wrote: Ok..... So you want to extract data where there is a value greater than 0 in *BOTH* Sales and Tax? Is the size of the table always the same? Always 4c x 316r ? What is the cell address where the table starts? For example, based on you sample Smith appears in cell A2. The reason I "need" to know this is because the formula will contain an expression based on this starting cell. A lot of times a poster will copy the formula from a reply and change this expression incorrectly and that ends up causing the formula to not work properly leading to a bunch of follow-up questions. This should be no problem. Biff "Sunny" wrote in message ... I have a list 4 x 316: county code sales tax smith 36-02 25.00 5.00 allen 14-17 14.00 1.40 caddo 72-36 .00 .00 park 55 -92 97.00 9.70 In a different part of the spread sheet I would like: code sales tax county 36-02 25.00 5.00 smith 14-17 14.00 1.40 allen 55-92 97.00 9.70 park I would like this list updated 'automatically' when something is changed in the top part. The numbers in the top are looked up on another spreadsheet and populated upon opening of the workbook. Do you need anything else? Thanks for your help "Biff" wrote: Hi! How about explaining exactly what you want to do (include the DETAILS!) and if I can figure it out I'll post a fresh link with a sample. Biff "Sunny" wrote in message ... This is exactly what I would liek to do! However, the link no longer works. Would you please send it again? Thanks- sunny "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Sorry it took so long . . . insane week.
This works exactly like I want it to on the lines I got from your example. I can't seem to make the copy to other lines work. Is there a trick? "Biff" wrote: Ok, here you go: Sample file: Sample extract.xls 18.5kb http://cjoint.com/?ipe25QHXQb I took a little bit of a different approach on this one. I saw your other post!!!!! If you have 50 sheets and they average 300 rows one array formula copied to that many cells would really slow things down! So, I used just one array formula in column X and then simple Vlookups in columns Y and Z and then a simple Index/Match in column AA. Also, another reason for the different formula is that you want the extracted data in a slightly different sequence. I'm assuming that the "code" is unique to the county? That's what I based the lookup formulas on. So, all you should have to do is change the references to the actual end of range of your data. You'll have to copy the formulas down to enough rows so that they pick up all the related data. Typically, how many entries will have 0 sales? 10? 20? 200? That's how many rows you would need to copy the formulas to. Are you familar with array formulas? How to enter an array formula: http://cpearson.com/excel/array.htm Biff "Sunny" wrote in message ... thanks! "Biff" wrote: Ok, no problem! I'll be away from the computer for a few hours so check back later. Biff "Sunny" wrote in message ... yes and only the sales column needs to be checked for 0 "Biff" wrote: can land anyplace right of W What does that mean? You want the extracted data to be placed anywhere to the right of column W? Biff "Sunny" wrote in message ... 316 is a constant number. It starts in A4 and can land anyplace right of W. "Biff" wrote: Ok..... So you want to extract data where there is a value greater than 0 in *BOTH* Sales and Tax? Is the size of the table always the same? Always 4c x 316r ? What is the cell address where the table starts? For example, based on you sample Smith appears in cell A2. The reason I "need" to know this is because the formula will contain an expression based on this starting cell. A lot of times a poster will copy the formula from a reply and change this expression incorrectly and that ends up causing the formula to not work properly leading to a bunch of follow-up questions. This should be no problem. Biff "Sunny" wrote in message ... I have a list 4 x 316: county code sales tax smith 36-02 25.00 5.00 allen 14-17 14.00 1.40 caddo 72-36 .00 .00 park 55 -92 97.00 9.70 In a different part of the spread sheet I would like: code sales tax county 36-02 25.00 5.00 smith 14-17 14.00 1.40 allen 55-92 97.00 9.70 park I would like this list updated 'automatically' when something is changed in the top part. The numbers in the top are looked up on another spreadsheet and populated upon opening of the workbook. Do you need anything else? Thanks for your help "Biff" wrote: Hi! How about explaining exactly what you want to do (include the DETAILS!) and if I can figure it out I'll post a fresh link with a sample. Biff "Sunny" wrote in message ... This is exactly what I would liek to do! However, the link no longer works. Would you please send it again? Thanks- sunny "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Not sure I understand what you mean?
Biff "Sunny" wrote in message ... Sorry it took so long . . . insane week. This works exactly like I want it to on the lines I got from your example. I can't seem to make the copy to other lines work. Is there a trick? "Biff" wrote: Ok, here you go: Sample file: Sample extract.xls 18.5kb http://cjoint.com/?ipe25QHXQb I took a little bit of a different approach on this one. I saw your other post!!!!! If you have 50 sheets and they average 300 rows one array formula copied to that many cells would really slow things down! So, I used just one array formula in column X and then simple Vlookups in columns Y and Z and then a simple Index/Match in column AA. Also, another reason for the different formula is that you want the extracted data in a slightly different sequence. I'm assuming that the "code" is unique to the county? That's what I based the lookup formulas on. So, all you should have to do is change the references to the actual end of range of your data. You'll have to copy the formulas down to enough rows so that they pick up all the related data. Typically, how many entries will have 0 sales? 10? 20? 200? That's how many rows you would need to copy the formulas to. Are you familar with array formulas? How to enter an array formula: http://cpearson.com/excel/array.htm Biff "Sunny" wrote in message ... thanks! "Biff" wrote: Ok, no problem! I'll be away from the computer for a few hours so check back later. Biff "Sunny" wrote in message ... yes and only the sales column needs to be checked for 0 "Biff" wrote: can land anyplace right of W What does that mean? You want the extracted data to be placed anywhere to the right of column W? Biff "Sunny" wrote in message ... 316 is a constant number. It starts in A4 and can land anyplace right of W. "Biff" wrote: Ok..... So you want to extract data where there is a value greater than 0 in *BOTH* Sales and Tax? Is the size of the table always the same? Always 4c x 316r ? What is the cell address where the table starts? For example, based on you sample Smith appears in cell A2. The reason I "need" to know this is because the formula will contain an expression based on this starting cell. A lot of times a poster will copy the formula from a reply and change this expression incorrectly and that ends up causing the formula to not work properly leading to a bunch of follow-up questions. This should be no problem. Biff "Sunny" wrote in message ... I have a list 4 x 316: county code sales tax smith 36-02 25.00 5.00 allen 14-17 14.00 1.40 caddo 72-36 .00 .00 park 55 -92 97.00 9.70 In a different part of the spread sheet I would like: code sales tax county 36-02 25.00 5.00 smith 14-17 14.00 1.40 allen 55-92 97.00 9.70 park I would like this list updated 'automatically' when something is changed in the top part. The numbers in the top are looked up on another spreadsheet and populated upon opening of the workbook. Do you need anything else? Thanks for your help "Biff" wrote: Hi! How about explaining exactly what you want to do (include the DETAILS!) and if I can figure it out I'll post a fresh link with a sample. Biff "Sunny" wrote in message ... This is exactly what I would liek to do! However, the link no longer works. Would you please send it again? Thanks- sunny "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Normally I just drag down to other cells. When I do that with this formula,
I get #REF in the destination cells. I even tried copy and paste. What other way is there to get a perfect formula into subsequent rows? sunny "Biff" wrote: Not sure I understand what you mean? Biff "Sunny" wrote in message ... Sorry it took so long . . . insane week. This works exactly like I want it to on the lines I got from your example. I can't seem to make the copy to other lines work. Is there a trick? "Biff" wrote: Ok, here you go: Sample file: Sample extract.xls 18.5kb http://cjoint.com/?ipe25QHXQb I took a little bit of a different approach on this one. I saw your other post!!!!! If you have 50 sheets and they average 300 rows one array formula copied to that many cells would really slow things down! So, I used just one array formula in column X and then simple Vlookups in columns Y and Z and then a simple Index/Match in column AA. Also, another reason for the different formula is that you want the extracted data in a slightly different sequence. I'm assuming that the "code" is unique to the county? That's what I based the lookup formulas on. So, all you should have to do is change the references to the actual end of range of your data. You'll have to copy the formulas down to enough rows so that they pick up all the related data. Typically, how many entries will have 0 sales? 10? 20? 200? That's how many rows you would need to copy the formulas to. Are you familar with array formulas? How to enter an array formula: http://cpearson.com/excel/array.htm Biff "Sunny" wrote in message ... thanks! "Biff" wrote: Ok, no problem! I'll be away from the computer for a few hours so check back later. Biff "Sunny" wrote in message ... yes and only the sales column needs to be checked for 0 "Biff" wrote: can land anyplace right of W What does that mean? You want the extracted data to be placed anywhere to the right of column W? Biff "Sunny" wrote in message ... 316 is a constant number. It starts in A4 and can land anyplace right of W. "Biff" wrote: Ok..... So you want to extract data where there is a value greater than 0 in *BOTH* Sales and Tax? Is the size of the table always the same? Always 4c x 316r ? What is the cell address where the table starts? For example, based on you sample Smith appears in cell A2. The reason I "need" to know this is because the formula will contain an expression based on this starting cell. A lot of times a poster will copy the formula from a reply and change this expression incorrectly and that ends up causing the formula to not work properly leading to a bunch of follow-up questions. This should be no problem. Biff "Sunny" wrote in message ... I have a list 4 x 316: county code sales tax smith 36-02 25.00 5.00 allen 14-17 14.00 1.40 caddo 72-36 .00 .00 park 55 -92 97.00 9.70 In a different part of the spread sheet I would like: code sales tax county 36-02 25.00 5.00 smith 14-17 14.00 1.40 allen 55-92 97.00 9.70 park I would like this list updated 'automatically' when something is changed in the top part. The numbers in the top are looked up on another spreadsheet and populated upon opening of the workbook. Do you need anything else? Thanks for your help "Biff" wrote: Hi! How about explaining exactly what you want to do (include the DETAILS!) and if I can figure it out I'll post a fresh link with a sample. Biff "Sunny" wrote in message ... This is exactly what I would liek to do! However, the link no longer works. Would you please send it again? Thanks- sunny "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a |
Getting all rows of data that have a value in a particular col
Can you send me a copy of your file? I don't need the whole thing if it's a
huge file, just the sheet where you're getting these errors. The only thing that I can think of that would cause #REF! errors is if you were somehow returning references that were outside of the range in the INDEX portion of the formula. If you can send me the file I'm at: xl can help at comcast period net Remove "can" and change the obvious. Biff "Sunny" wrote in message ... Normally I just drag down to other cells. When I do that with this formula, I get #REF in the destination cells. I even tried copy and paste. What other way is there to get a perfect formula into subsequent rows? sunny "Biff" wrote: Not sure I understand what you mean? Biff "Sunny" wrote in message ... Sorry it took so long . . . insane week. This works exactly like I want it to on the lines I got from your example. I can't seem to make the copy to other lines work. Is there a trick? "Biff" wrote: Ok, here you go: Sample file: Sample extract.xls 18.5kb http://cjoint.com/?ipe25QHXQb I took a little bit of a different approach on this one. I saw your other post!!!!! If you have 50 sheets and they average 300 rows one array formula copied to that many cells would really slow things down! So, I used just one array formula in column X and then simple Vlookups in columns Y and Z and then a simple Index/Match in column AA. Also, another reason for the different formula is that you want the extracted data in a slightly different sequence. I'm assuming that the "code" is unique to the county? That's what I based the lookup formulas on. So, all you should have to do is change the references to the actual end of range of your data. You'll have to copy the formulas down to enough rows so that they pick up all the related data. Typically, how many entries will have 0 sales? 10? 20? 200? That's how many rows you would need to copy the formulas to. Are you familar with array formulas? How to enter an array formula: http://cpearson.com/excel/array.htm Biff "Sunny" wrote in message ... thanks! "Biff" wrote: Ok, no problem! I'll be away from the computer for a few hours so check back later. Biff "Sunny" wrote in message ... yes and only the sales column needs to be checked for 0 "Biff" wrote: can land anyplace right of W What does that mean? You want the extracted data to be placed anywhere to the right of column W? Biff "Sunny" wrote in message ... 316 is a constant number. It starts in A4 and can land anyplace right of W. "Biff" wrote: Ok..... So you want to extract data where there is a value greater than 0 in *BOTH* Sales and Tax? Is the size of the table always the same? Always 4c x 316r ? What is the cell address where the table starts? For example, based on you sample Smith appears in cell A2. The reason I "need" to know this is because the formula will contain an expression based on this starting cell. A lot of times a poster will copy the formula from a reply and change this expression incorrectly and that ends up causing the formula to not work properly leading to a bunch of follow-up questions. This should be no problem. Biff "Sunny" wrote in message ... I have a list 4 x 316: county code sales tax smith 36-02 25.00 5.00 allen 14-17 14.00 1.40 caddo 72-36 .00 .00 park 55 -92 97.00 9.70 In a different part of the spread sheet I would like: code sales tax county 36-02 25.00 5.00 smith 14-17 14.00 1.40 allen 55-92 97.00 9.70 park I would like this list updated 'automatically' when something is changed in the top part. The numbers in the top are looked up on another spreadsheet and populated upon opening of the workbook. Do you need anything else? Thanks for your help "Biff" wrote: Hi! How about explaining exactly what you want to do (include the DETAILS!) and if I can figure it out I'll post a fresh link with a sample. Biff "Sunny" wrote in message ... This is exactly what I would liek to do! However, the link no longer works. Would you please send it again? Thanks- sunny "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a |
Getting all rows of data that have a value in a particular col
Hi,
I am trying to do something similar. I want to call values from another worksheet but ignore the values that are "n/a". The link you posted does not have the formula anymore. Thanks for your time, UT "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Can you provide some more specific details?
Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ? Biff "UT" wrote in message ... Hi, I am trying to do something similar. I want to call values from another worksheet but ignore the values that are "n/a". The link you posted does not have the formula anymore. Thanks for your time, UT "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Hi,
The "n/a" values are valid text entry. However in another worksheet I want to ignore the "n/a" text and only link to other cell values in that column. I have read about the formatting the not required values in white font in the conditional formatting. But that does not serve my purpose. I only want to link to the text values other than "n/a". Is there a way to do this? Thanks. "T. Valko" wrote: Can you provide some more specific details? Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ? Biff "UT" wrote in message ... Hi, I am trying to do something similar. I want to call values from another worksheet but ignore the values that are "n/a". The link you posted does not have the formula anymore. Thanks for your time, UT "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Let's assume you have this data in A1:A5 -
n/a blue n/a n/a red You want to extract all entries that are not n/a to another location. If there are no empty/blank cells as above try this array** formula: =IF(ROWS($1:1)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"") Copy down until you get blanks. If there might be empty/blank cells try this array** formula: =IF(ROWS($1:1)<=SUMPRODUCT(--(rng<""),--(rng<"n/a")),INDEX(rng,SMALL(IF((rng<"n/a")*(rng<""),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"") Copy down until you get blanks. ** array formulas *MUST* be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "UT" wrote in message ... Hi, The "n/a" values are valid text entry. However in another worksheet I want to ignore the "n/a" text and only link to other cell values in that column. I have read about the formatting the not required values in white font in the conditional formatting. But that does not serve my purpose. I only want to link to the text values other than "n/a". Is there a way to do this? Thanks. "T. Valko" wrote: Can you provide some more specific details? Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ? Biff "UT" wrote in message ... Hi, I am trying to do something similar. I want to call values from another worksheet but ignore the values that are "n/a". The link you posted does not have the formula anymore. Thanks for your time, UT "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Hi Biff:
I tried the following formula but it didn't work. It give "name" error. What am I doing wrong? Thanks =IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS(GISData!H$8:H$500))),"") "T. Valko" wrote: Let's assume you have this data in A1:A5 - n/a blue n/a n/a red You want to extract all entries that are not n/a to another location. If there are no empty/blank cells as above try this array** formula: =IF(ROWS($1:1)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"") Copy down until you get blanks. If there might be empty/blank cells try this array** formula: =IF(ROWS($1:1)<=SUMPRODUCT(--(rng<""),--(rng<"n/a")),INDEX(rng,SMALL(IF((rng<"n/a")*(rng<""),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"") Copy down until you get blanks. ** array formulas *MUST* be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "UT" wrote in message ... Hi, The "n/a" values are valid text entry. However in another worksheet I want to ignore the "n/a" text and only link to other cell values in that column. I have read about the formatting the not required values in white font in the conditional formatting. But that does not serve my purpose. I only want to link to the text values other than "n/a". Is there a way to do this? Thanks. "T. Valko" wrote: Can you provide some more specific details? Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ? Biff "UT" wrote in message ... Hi, I am trying to do something similar. I want to call values from another worksheet but ignore the values that are "n/a". The link you posted does not have the formula anymore. Thanks for your time, UT "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
In the formula where you see rng << This is referring to a named
Range, which in your case is Range(A1:A5). Select the Range A1:A5 (and while selected (In the NamedRange Box type in "rng" (without the quotes), thereby creating a named range equal to Range A1:A5) then the formulas will work,,, HTH "UT" wrote in message : Hi Biff: I tried the following formula but it didn't work. It give "name" error. What am I doing wrong? Thanks =IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS(GISData!H$8:H$500))),"") "T. Valko" wrote: Let's assume you have this data in A1:A5 - n/a blue n/a n/a red You want to extract all entries that are not n/a to another location. If there are no empty/blank cells as above try this array** formula: =IF(ROWS($1:1)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"") Copy down until you get blanks. If there might be empty/blank cells try this array** formula: =IF(ROWS($1:1)<=SUMPRODUCT(--(rng<""),--(rng<"n/a")),INDEX(rng,SMALL(IF((rng<"n/a")*(rng<""),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"") Copy down until you get blanks. ** array formulas *MUST* be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "UT" wrote in message ... Hi, The "n/a" values are valid text entry. However in another worksheet I want to ignore the "n/a" text and only link to other cell values in that column. I have read about the formatting the not required values in white font in the conditional formatting. But that does not serve my purpose. I only want to link to the text values other than "n/a". Is there a way to do this? Thanks. "T. Valko" wrote: Can you provide some more specific details? Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ? Biff "UT" wrote in message ... Hi, I am trying to do something similar. I want to call values from another worksheet but ignore the values that are "n/a". The link you posted does not have the formula anymore. Thanks for your time, UT "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Thanks for the tip. I had never used range before.
I named my column range - "rng1" (without quotes), in the other worksheet. In this worksheet I entered the range name in the following formula. There are blank cells and cells with "n/a" value that I want to ignore. The formula still doesn't work though. The cell remains blank after I entered this formula. =IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng1,"<n/a"),INDEX(rng1,SMALL(IF(rng1<"n/a",ROW(rng1)-MIN(ROW(rng1))+1),ROWS(GISData!H$8:H$500))),"") Thanks for your help. "JMay" wrote: In the formula where you see rng << This is referring to a named Range, which in your case is Range(A1:A5). Select the Range A1:A5 (and while selected (In the NamedRange Box type in "rng" (without the quotes), thereby creating a named range equal to Range A1:A5) then the formulas will work,,, HTH "UT" wrote in message : Hi Biff: I tried the following formula but it didn't work. It give "name" error. What am I doing wrong? Thanks =IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS(GISData!H$8:H$500))),"") "T. Valko" wrote: Let's assume you have this data in A1:A5 - n/a blue n/a n/a red You want to extract all entries that are not n/a to another location. If there are no empty/blank cells as above try this array** formula: =IF(ROWS($1:1)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"") Copy down until you get blanks. If there might be empty/blank cells try this array** formula: =IF(ROWS($1:1)<=SUMPRODUCT(--(rng<""),--(rng<"n/a")),INDEX(rng,SMALL(IF((rng<"n/a")*(rng<""),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"") Copy down until you get blanks. ** array formulas *MUST* be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "UT" wrote in message ... Hi, The "n/a" values are valid text entry. However in another worksheet I want to ignore the "n/a" text and only link to other cell values in that column. I have read about the formatting the not required values in white font in the conditional formatting. But that does not serve my purpose. I only want to link to the text values other than "n/a". Is there a way to do this? Thanks. "T. Valko" wrote: Can you provide some more specific details? Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ? Biff "UT" wrote in message ... Hi, I am trying to do something similar. I want to call values from another worksheet but ignore the values that are "n/a". The link you posted does not have the formula anymore. Thanks for your time, UT "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
Change each instance of:
ROWS(GISData!H$8:H$500) To: ROWS($1:1) ROWS($1:1) is just a "counter" and has nothing to do with the actual range GISData!H$8:H$500 Biff "UT" wrote in message ... Thanks for the tip. I had never used range before. I named my column range - "rng1" (without quotes), in the other worksheet. In this worksheet I entered the range name in the following formula. There are blank cells and cells with "n/a" value that I want to ignore. The formula still doesn't work though. The cell remains blank after I entered this formula. =IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng1,"<n/a"),INDEX(rng1,SMALL(IF(rng1<"n/a",ROW(rng1)-MIN(ROW(rng1))+1),ROWS(GISData!H$8:H$500))),"") Thanks for your help. "JMay" wrote: In the formula where you see rng << This is referring to a named Range, which in your case is Range(A1:A5). Select the Range A1:A5 (and while selected (In the NamedRange Box type in "rng" (without the quotes), thereby creating a named range equal to Range A1:A5) then the formulas will work,,, HTH "UT" wrote in message : Hi Biff: I tried the following formula but it didn't work. It give "name" error. What am I doing wrong? Thanks =IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS(GISData!H$8:H$500))),"") "T. Valko" wrote: Let's assume you have this data in A1:A5 - n/a blue n/a n/a red You want to extract all entries that are not n/a to another location. If there are no empty/blank cells as above try this array** formula: =IF(ROWS($1:1)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"") Copy down until you get blanks. If there might be empty/blank cells try this array** formula: =IF(ROWS($1:1)<=SUMPRODUCT(--(rng<""),--(rng<"n/a")),INDEX(rng,SMALL(IF((rng<"n/a")*(rng<""),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"") Copy down until you get blanks. ** array formulas *MUST* be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "UT" wrote in message ... Hi, The "n/a" values are valid text entry. However in another worksheet I want to ignore the "n/a" text and only link to other cell values in that column. I have read about the formatting the not required values in white font in the conditional formatting. But that does not serve my purpose. I only want to link to the text values other than "n/a". Is there a way to do this? Thanks. "T. Valko" wrote: Can you provide some more specific details? Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ? Biff "UT" wrote in message ... Hi, I am trying to do something similar. I want to call values from another worksheet but ignore the values that are "n/a". The link you posted does not have the formula anymore. Thanks for your time, UT "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular col
The formula worked perfectly. Thanks a ton.
One more question, my worksheet is linked to sql server database. Everytime I refresh data on the worksheet the column width is adjusted to the column heading size. I have tried to specify the column width but it changes upon refreshing the data. Is there a way to lock the column width or row height. Thanks. "T. Valko" wrote: Change each instance of: ROWS(GISData!H$8:H$500) To: ROWS($1:1) ROWS($1:1) is just a "counter" and has nothing to do with the actual range GISData!H$8:H$500 Biff "UT" wrote in message ... Thanks for the tip. I had never used range before. I named my column range - "rng1" (without quotes), in the other worksheet. In this worksheet I entered the range name in the following formula. There are blank cells and cells with "n/a" value that I want to ignore. The formula still doesn't work though. The cell remains blank after I entered this formula. =IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng1,"<n/a"),INDEX(rng1,SMALL(IF(rng1<"n/a",ROW(rng1)-MIN(ROW(rng1))+1),ROWS(GISData!H$8:H$500))),"") Thanks for your help. "JMay" wrote: In the formula where you see rng << This is referring to a named Range, which in your case is Range(A1:A5). Select the Range A1:A5 (and while selected (In the NamedRange Box type in "rng" (without the quotes), thereby creating a named range equal to Range A1:A5) then the formulas will work,,, HTH "UT" wrote in message : Hi Biff: I tried the following formula but it didn't work. It give "name" error. What am I doing wrong? Thanks =IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS(GISData!H$8:H$500))),"") "T. Valko" wrote: Let's assume you have this data in A1:A5 - n/a blue n/a n/a red You want to extract all entries that are not n/a to another location. If there are no empty/blank cells as above try this array** formula: =IF(ROWS($1:1)<=COUNTIF(rng,"<n/a"),INDEX(rng,SMALL(IF(rng<"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"") Copy down until you get blanks. If there might be empty/blank cells try this array** formula: =IF(ROWS($1:1)<=SUMPRODUCT(--(rng<""),--(rng<"n/a")),INDEX(rng,SMALL(IF((rng<"n/a")*(rng<""),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"") Copy down until you get blanks. ** array formulas *MUST* be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "UT" wrote in message ... Hi, The "n/a" values are valid text entry. However in another worksheet I want to ignore the "n/a" text and only link to other cell values in that column. I have read about the formatting the not required values in white font in the conditional formatting. But that does not serve my purpose. I only want to link to the text values other than "n/a". Is there a way to do this? Thanks. "T. Valko" wrote: Can you provide some more specific details? Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ? Biff "UT" wrote in message ... Hi, I am trying to do something similar. I want to call values from another worksheet but ignore the values that are "n/a". The link you posted does not have the formula anymore. Thanks for your time, UT "Biff" wrote: Here's a link to a sample file: http://s40.yousendit.com/d.aspx?id=2...U3RXRP2RI1ACPU There are a few different formula methods that could be used. This demonstrates the method I prefer. Just one formula copied to the cells. It's an array formula, though, and array formulas take longer to calculate. Depending on the size of your file and other factors, you might not notice any difference. Try deleting some data in column F then look at sheet 2 to see how it updates. If in your situation you expect that there may be 20-40 rows that meet the criteria, then you'd need to copy the formula to AT LEAST 40 rows. In the sample I copied the formula to 10 rows by 10 columns. Biff "Biff" wrote in message ... Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? That should be Ok but you'll have to see how it affects performance and then make that determination for yourself. I'm going to break for dinner but I'll put together a sample file that demonstrates this (unless Max beats me to it!<g) and post a link to the file. Biff "Rikki-Handgards" wrote in message ... Thanks Biff. Would it still be practical if I had 250 rows of data total and needed to pull in 10 columns for only about 20-40 rows? "Biff" wrote: Is there a way to do this with a formula and not a pivot table? Yes, but whether or not it's practical depends on how many cells from the row you need returned and in general, how big is the table of data this data is being extracted from. For example, if your table is 50,000 rows by 150 columns and you need all 150 columns of data extracted for each instance of "value", then a formula approach is not practical. Biff "Rikki-Handgards" wrote in message ... Hi. Is there a way to list, on a separate sheet, only rows of data with a value in a specific column? I want to list inventory items (and all data in that row) as long as there is a value in column F. If there is no value in column F for that inventory item, I don't want it to be the separate summary sheet. Is there a way to do this with a formula and not a pivot table? Thanks! |
Getting all rows of data that have a value in a particular column
I have exactly the same question. I have the list for inventory and if the
item number is lower than reorder level, then there will be a "warning" written at the beginning of the row automatically. Then I want to copy those rows with "warning" to another sheet, then I can generate my reorder list. So how can I do?? Thank you! |
| All times are GMT +1. The time now is 08:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com