Extract data by skipping blanks into a summarized form

[Guest]

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

 

[Randy Harmelink]

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<>' ')

 

[Ken Johnson]

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