Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract data by skipping blanks into a summarized form
Hi All,
I have a raw data set that contains 4 columns of data, some with blanks, some without. What formula will allow me to extract only nonblank cells, skipping the blanks. So instead of having a list of 500 rows with tons of blanks, I want to list - on a separate tab - 50 rows of nonblanks. I can't use pivot tables because I need more functionality, and can't use autofilter because for any one row - i may have a blank in the first column but a dollar amount in the next. I prefer not to collapse the source data - I want to let that come in just as it is since it's external query. My lookup formula will reside on a different tab. Help :) If I didn't explain it clearly, please let me know...thanks so much :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract data by skipping blanks into a summarized form
Just set up a "New Database Query" based on your input data. You can
use SQL similar to this: SELECT `Sheet1$`.Field1, `Sheet1$`.Field2, `Sheet1$`.Field3, `Sheet1$`.Field4 FROM `Sheet1$` `Sheet1$` WHERE (`Sheet1$`.Field1<' ') OR (`Sheet1$`.Field2<' ') OR (`Sheet1$`.Field3<' ') OR (`Sheet1$`.Field4<' ') |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract data by skipping blanks into a summarized form
Hi Alina,
Are you wanting to get rid of the rows that are blank in all four columns? If that is the case, say the data columns are A,B,C and D, then in any spare column, preferably E, you could use the following formula, starting at row 1 and filling down to the bottom of your data then use autofilter on that column to hide the blanks. Then you could copy and paste the filtered data to the new sheet... =IF(AND(A1="",B1="",C1="",D1=""),"","not all blank") Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Skipping Blanks (Again) | Excel Discussion (Misc queries) | |||
Skipping Blanks | Excel Discussion (Misc queries) | |||
Extract Data into a Summarized form | Excel Programming | |||
Skipping blanks | Excel Discussion (Misc queries) | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) |