Extracting data help

[rjc4]

Hi,
My test range is AL2:AS20 in which I store my data. All the cells are formatted as General.

Some rows are completely blank (i.e.contain no data at all).

Other rows have some cells containing data.

How can I extract ONLY those rows that have data in EVERY CELL and then sort by AL (This column is a number column) so I am left with only the rows with every cell populated.

It's driving me mad! Could anyone help, please.
Thankyou

 

[excelsishya]

Hi,

At AT2 =AND(IF(AL2<>"","true"),AM2<>"","true",AN2<>"","true",AO2<>"","true",AP2<>"","true",AQ2<>"","true",AR2<>"","true",AS2<>"","true")
use this formula and copy down

Then filter all false in column AT (helper column) and delete those row.

You will be left with rows containing data in each of its cell then u can sort in AL column.

 

[Peter_SSs]

Hi,

At AT2 =AND(IF(AL2<>"","true"),AM2<>"","true",AN2<>"","true",AO2<>"","true",AP2<>"","true",AQ2<>"","true",AR2<>"","true",AS2<>"","true")
use this formula and copy down

Then filter all false in column AT (helper column) and delete those row.

You will be left with rows containing data in each of its cell then u can sort in AL column.

Probably simpler to use something like this in AT2:
=COUNTBLANK(AL17:AS17)=0
and filter for 'FALSE' values to delete the rows.

 

[cgcamal]

Hi rjc4,

Similarly, in AK2 or AT2 put and copy down.

=IF([COLOR=Blue]COUNTA([COLOR=Red]AL2:AS2[/COLOR])=8,1,0[/COLOR])

Then apply a filter over the 1's and finally sort by AL and copy visible lines.

Regards

 

[rjc4]

Thanks for everyone's replies. I'm working through them now. But using the filter changes my spreadsheet layout. I was hoping to incorporate the solution into a macro that I could use with my existing macros. Could anyone enlighten me on this aspect, please.
Thank you

 

[Peter_SSs]

Another pretty quick option.

1. Copy data to another location first if you want to keep the original.

2. Select the whole range.

3. Go To Special...|Blanks (where this is located depends on which Excel version you have)

4. Right click on one of the grey selected cells|Delete...|Entire row|OK

 

[rjc4]

Hello Peter,
Thanks for your help
I'm using Excel97 and I don't seem to have Special. At least I can't find it.

All the solutions posted work as far as highlighting the data that is not required (thank you everyone). I can see TRUE or FALSE or 1 etc and I could probably put this in a macro myself.

But I'm stuck here. The filter puts drop down boxes at the top of my sheet which I don't want. I just want to now eliminate all the records with FALSE beside them in column AT.

I can select the range, use the auto filter to just get TRUE, which is what I want, but when I turn off auto filter, my data is reinserted. So how do I now get rid of the drop down boxes on my sheet.

Any thoughts!

Thanks

 

[Peter_SSs]

Here's a different (macro) approach. No idea if it will work with Excel 97 (nearly time you updated )

Test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> KeepFullRows()<br>    <SPAN style="color:#00007F">Dim</SPAN> B <SPAN style="color:#00007F">As</SPAN> Range, Bfound <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> B = Rows(1)<br>    <br>    <SPAN style="color:#00007F">For</SPAN> r = 2 <SPAN style="color:#00007F">To</SPAN> 20<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> Bfound = Range("AL" & r).Resize(, 8).SpecialCells(xlCellTypeBlanks)<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Bfound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> B = Union(B, Rows(r))<br>            <SPAN style="color:#00007F">Set</SPAN> Bfound = <SPAN style="color:#00007F">Nothing</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    <SPAN style="color:#00007F">Set</SPAN> B = Intersect(B, Rows("2:20"))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> B <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> B.Delete<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[rjc4]

Thanks Peter,
I'm trying this now. I'm a bit slow with this stuff. The filter approach works a treat, but even using the instructions in the Filter help of Excel, I still can't remove the filter without putting the original data back. I know I'm doing something dumb, but I'll have a go at your macro and see if that can get me out of trouble.
Cheers

ps Have just run macro but there appears to be no response. Nothing happens. I entered a cell range in to the macro to make sure it was working and the cursor goes to that cell.

 

[rjc4]

I've tried the Filter approach and although it does highlight my required data, it just appears to truncate it. That is, it just removes the unwanted rows from being visible and when clicking Autofilter to remove the drop down filter arrows, the data reappears. I'm trying to remove the data, not hide it. So i think Peter is on to something with his macro, but I have had no joy in getting it to work so far. Still open for suggestions. And thanks so far for everyone's interest.