| Home |
| Search |
| Today's Posts |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
| In Excel: select the last 20 rows of data in a column | Excel Worksheet Functions | |||
| Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
| How can I convert a data from multiple rows into 1 column? | Excel Worksheet Functions | |||
| find rows for unique data in 1 column and different data in other. | Excel Discussion (Misc queries) | |||
Threaded Mode
