If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Finding data amongst empty cells
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. |
#2
|
|||
|
|||
Finding data amongst empty cells
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. |
#3
|
|||
|
|||
Finding data amongst empty cells
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. |
#4
|
|||
|
|||
Finding data amongst empty cells
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. |
#5
|
|||
|
|||
Finding data amongst empty cells
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?! |
#6
|
|||
|
|||
Finding data amongst empty cells
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?! |
#7
|
|||
|
|||
Finding data amongst empty cells
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?! |
Thread Tools | |
Display Modes | |
|
|