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(Bl anksRange))),
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

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,SM ALL($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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in message
news:9ec01f21-eace-4d63-94d0-80fe6aa770b5@y38g2000hsy.googlegroups.com...
> 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(Bl anksRange))),
> 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

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)

--
Biff
Microsoft Excel MVP


"J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in message
news:9ec01f21-eace-4d63-94d0-80fe6aa770b5@y38g2000hsy.googlegroups.com...
> 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(Bl anksRange))),
> 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

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

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?

--
Biff
Microsoft Excel MVP


"J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in message
news:9ee467c9-72a0-4f98-b21f-6b05f52508ba@y38g2000hsy.googlegroups.com...
> 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).
>
>
>

List1 = (F$19:F5000)

The formula starts at: G$19:G5000



Thanx.

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

Entered in G19 and copied down:

=IF(ROWS(G$19:G19)<=COUNTIF(List1,"?*"),INDEX(List 1,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(List 1<>"",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(List 1<>"",ROW(List1)-18),ROWS(G$19:G19))),"")

The "big" formulas are all still array entered.

--
Biff
Microsoft Excel MVP


"J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in message
news:5b322aef-8969-4086-97a4-90573309c3fa@t54g2000hsg.googlegroups.com...
>
> List1 = (F$19:F5000)
>
> The formula starts at: G$19:G5000
>
>
>
> Thanx.

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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in message
news:ae7cfd98-91e4-4cd1-af32-e301eb1b8c08@c65g2000hsa.googlegroups.com...
> 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!)