Extract data from list -exluding blank cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J.W. Aldridge - 02 May 2008 19:40 GMT

Tried the C.Pearson site.... Couldn't get that formula to work.

=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

Need to sort order of results from another formula into a list without
blank cells.

List #1 List #2
cherries cherries
plums plums
apples apples
oranges
oranges grapes
grapes lemons
lemons

Reply to this Message

Max - 02 May 2008 21:42 GMT

Perhaps a simpler non-array alternative that gets you there ..

Source data (List#1) assumed in Sheet1,
cols A & B, data from row2 down

In Sheet2,
In A2: =IF(TRIM(Sheet1!A2)="","",ROW())
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROWS($1:1))))
Copy A2:B2 down to cover the max expected extent of data in Sheet1.
Minimize/hide away col A. Col B returns the required results, all neatly
bunched at the top.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Tried the C.Pearson site.... Couldn't get that formula to work.
>
[quoted text clipped - 14 lines]
> grapes lemons
> lemons

Reply to this Message

T. Valko - 02 May 2008 22:09 GMT

List1 in the range A2:A8.

Enter this array formula** in B2 and copy down until you get blanks:

=IF(ROWS(B$2:B2)<=COUNTA(List1),INDEX(List1,SMALL(IF(List1<>"",ROW(List1)-MIN(ROW(List1))+1),ROWS(B$2:B2))),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Signature

Biff
Microsoft Excel MVP

> Tried the C.Pearson site.... Couldn't get that formula to work.
>
[quoted text clipped - 14 lines]
> grapes lemons
> lemons

Reply to this Message

J.W. Aldridge - 02 May 2008 22:28 GMT

Thanx Biff.

Does it matter if A2:A8 contains results from formulas and not entered
numbers?

All get I get is blanks.

(Actually found another formula that eliminates blanks and it didnt
work when refering to results of a formula either).

Reply to this Message

T. Valko - 02 May 2008 22:35 GMT

In general that should not matter. However, if those formulas return formula
blanks ("") then the formula I suggested will need to be tweaked since I
wrote it assuming "blanks" meant *empty*.

What is the *exact* location of List1?

Signature

Biff
Microsoft Excel MVP

> Thanx Biff.
>
[quoted text clipped - 5 lines]
> (Actually found another formula that eliminates blanks and it didnt
> work when refering to results of a formula either).

Reply to this Message

J.W. Aldridge - 02 May 2008 22:48 GMT

List1 = (F$19:F5000)

The formula starts at: G$19:G5000

Thanx.

Reply to this Message

T. Valko - 02 May 2008 23:41 GMT

OK, assuming List1 contains only TEXT entries (like your sample):

Entered in G19 and copied down:

=IF(ROWS(G$19:G19)<=COUNTIF(List1,"?*"),INDEX(List1,SMALL(IF(List1<>"",ROW(List1)-MIN(ROW(List1))+1),ROWS(G$19:G19))),"")

Since your range is "kind of" big you might want to try this approach to
help save some resources:

Use a helper cell with this formula:

=COUNTIF(List1,"?*")

Then refer to that helper cell:

Helper cell = G18

=IF(ROWS(G$19:G19)<=G$18,INDEX(List1,SMALL(IF(List1<>"",ROW(List1)-MIN(ROW(List1))+1),ROWS(G$19:G19))),"")

Also, if you will *NEVER* insert new rows above the range we can eliminate
some other calculation intensive expressions:

=IF(ROWS(G$19:G19)<=G$18,INDEX(List1,SMALL(IF(List1<>"",ROW(List1)-18),ROWS(G$19:G19))),"")

The "big" formulas are all still array entered.

Signature

Biff
Microsoft Excel MVP

> List1 = (F$19:F5000)
>
> The formula starts at: G$19:G5000
>
> Thanx.

Reply to this Message

J.W. Aldridge - 02 May 2008 23:55 GMT

You ROCK!

When I become a millionaire.... I'm buying you TWO Double Cheese
Krystals (or Whitle Castles)!!

(a very high honor... very few people have ever gotten to the BIG TWO
level!)

Reply to this Message

T. Valko - 03 May 2008 02:50 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> You ROCK!
>
[quoted text clipped - 3 lines]
> (a very high honor... very few people have ever gotten to the BIG TWO
> level!)

Reply to this Message