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

 

[Peter_SSs]

Have just run macro but there appears to be no response. Nothing happens.

Can you confirm ..

1. The range we are supposed to be looking at is AL2:AS20?

2. The sheet in question was the active sheet when you ran the macro?

3. The 'blank' cells really are blank and do not contain
- formulas returning null strings (""), or
- 'blank' data copied from a web page?


In relation to the Autofilter approach, all the suggestions were to filter column AT for the value(s) you DON'T want, then delete those rows, then remove the AutoFilter to leave the values you wanted.

 

[rjc4]

Hello Peter,
I'll come back to the macro when I understand what is happening a bit better. I think my range is incorrect and incorporates some components you suggest I should look at.

In the meantime, I have used the Filter tool to highlight my data as TRUE or FALSE as advised by the forum members. With that as a base, I have then used formulas to extract my data and incorporated them in to a macro. It is working. Probably very clumsy, but it's a base to fine tune. Having said that, I've struck a similar problem with extracting some data from my finished list. I would like to use a formula if possible as I understand them better.

Basically, I have a list of amounts in A8:A20 in no particular order. Using a formula in B9:B20, I wish to identify the smallest amount in A8:A20 as FALSE and all others as TRUE.

Could you possibly help with this formula. Sorry to be such a nuisance!

Regards

 

[Peter_SSs]

Basically, I have a list of amounts in A8:A20 in no particular order. Using a formula in B9:B20, I wish to identify the smallest amount in A8:A20 as FALSE and all others as TRUE.

Could you possibly help with this formula. Sorry to be such a nuisance!

You aren't a nuisance - the forum is here for questions.

Is this what you want?

Excel Workbook
	A	B
8	3	TRUE
9	5	TRUE
10	6	TRUE
11	2	FALSE
12	2	FALSE
13	8	TRUE
14	15	TRUE
15	8	TRUE
16	4	TRUE
17	2	FALSE
18		TRUE
19	8	TRUE
20	9	TRUE
MIN FALSE

 

[rjc4]

Hi Peter,
That was a very quick reply. Thank you but not quite.

This is a sample from my data in A8 down.
39.30
11.00
20.80
3.90
5.80
5.90
8.00
3.40
I'm using this formula in B8 down which is what I want, but reversed. It puts TRUE against the 39.30 and FALSE against all the others. I want TRUE against the 3.40 and FALSE against all the others. That is, the lowest number in the list identified with the word TRUE and the rest FALSE.

=A8>=LARGE($A$8:$A$20,TRUE)

I've tried SMALL and MIN but must be doing something incorrectly as it returns #num.

Can you advise.

Cheers

 

[rjc4]

Hi Peter,
Got it! Used your formula and a work around.

Thank you very much for your help this evening.
Cheers,
Rick