Finding data amongst empty cells

[James]

Hi all

I have a column with 1,000 rows but only 10 of the rows contain any data and
they are spread out across the 1,000. On a seperate sheet I want to list
those 10 in a row together but I don't want to copy all 1,00 across and use
data sort as it needs to be formula based.

Any ideas?!

 

[Jarek Kujawa]

with your data in range A1:A1000 in Sheet1
one way might be to use this array-formula (insert with CTRL+SHIFT
+ENTER):

=OFFSET(Sheet1!$A$1,SMALL(IF(LEN(Sheet1!$A$1:$A$1000),ROW(Sheet1!$A
$1:$A$1000)),ROW())-1,)

pls click YES if it helped

 

[Don Guillett]

Would
Data>filter>autofilter>non blanks
help
if not
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Luke M]

Something like this array* formula:

=IF(ROWS($B$1:B1)>COUNTA(A:A),"",INDEX(A:A,SMALL(IF(ISTEXT(A$1:A$2000),ROW(A$1:A$2000)),ROWS($B$1:B1))))

Note that the ROWS function is only in there to keep track of how many
records have been used, it is not actually referring to any data. You need
only adjust the A$1:A$2000 and A:A callouts to the correct range to suit the
formula to your situation.

*Formula must be confirmed using Ctrl+Shift+Enter, not just Enter.

 

[Max]

Assume your source data is in Sheet1, in A2 down to A1000

In another sheet,
In A2: =IF(TRIM(Sheet1!A2)="","",ROW())
Copy A2 down to cover the same extent as the source, ie down to A1000.
Hide/minimize col A. The TRIM is optional, but I've thrown it in here to
rule out possibility of any white spaces within the source data.

Then place in B2:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A,ROWS($1:1))))
Copy B2 down to cover the max expected extent of results, ie down to B11
(since you expect only max 10 rows of results). The expected results will
appear all neatly packed at the top. Success? hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[James]

Thanks guys very helpful. Another challenge for you though, what if there are
2 columns not just one?!

 

[Luke M]

We can insert another IF statement to make sure all of the first column's
data has been collected.

=IF(ROWS($B$1:B1)>COUNTA(A:A),IF(ROWS($B$1:B1)>COUNTA(A:B),"",INDEX(B:B,SMALL(IF(ISTEXT(B$1:B$2000),ROW(B$1:B$2000)),ROWS($B$1:B1)-COUNTA(A:A)))),INDEX(A:A,SMALL(IF(ISTEXT(A$1:A$2000),ROW(A$1:A$2000)),ROWS($B$1:B1))))

Again, this is an array formula.