Extract data from list -exluding blank cells

[J.W. Aldridge]

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

 

[Max]

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.

 

[T. Valko]

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)

 

[J.W. Aldridge]

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

 

[T. Valko]

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?

 

[J.W. Aldridge]

List1 = (F$19:F5000)

The formula starts at: G$19:G5000



Thanx.

 

[T. Valko]

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.

 

[J.W. Aldridge]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!