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



--
James.

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$10 00),ROW(Sheet1!$A
$1:$A$1000)),ROW())-1,)

pls click YES if it helped


On 10 Lis, 14:28, James wrote:
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?!

--
James.

Would
Datafilterautofilternon 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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"James" wrote in message
...
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?!



--
James.

Something like this array* formula:

=IF(ROWS($B$1:B1)COUNTA(A:A),"",INDEX(A:A,SMALL(I F(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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"James" wrote:

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



--
James.

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,SMAL L(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" wrote:
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?!

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

--
James.


"Max" wrote:

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,SMAL L(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" wrote:
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?!

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)COU NTA(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$200 0),ROW(A$1:A$2000)),ROWS($B$1:B1))))

Again, this is an array formula.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"James" wrote:

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

--
James.


"Max" wrote:

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,SMAL L(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" wrote:
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?!