list data without blank rows

[john]

I have a spreadsheet full of data. I want to organize my data on a second tab
by looking in a single column and if a text value is present (such as an "M"
or a "K"), then copy data in the same row to the second tab. But not every
row will have a text value, some may be blank and I don't want a bunch of
blank rows on my second tab sheet. Any ideas??

 

[Max]

One way .. a simple, dynamic formulas option

Assume source data is in Sheet1, cols A to C, data from row2 down
Assume the key col is col B (ie your "single" col to check whether there is
a text value present)

In Sheet2,
In A2: =IF(AND(ISTEXT(Sheet1!B2),Sheet1!B2<>""),ROW(),"")
This is the criteria, with an added check to the ISTEXT, ie: ..
Sheet1!B2<>"", to exclude possibility of zero length null strings: ""
returned by formulas in Sheet1's col B

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROWS($1:1))))
Copy B2 to D2. Select A22, fill down to cover the max expected extent of
source data, say down to D100? Minimize/hide away the criteria col A. Cols B
to D will return the required results, with lines all neatly packed at the top

Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[john]

thankyou for the post, this works, but is there a way to do it without
creating column A?

 

[Gary Byrne]

Hi John,

There are several ways to go about this... you could simply copy the data to the second tab, and sort by the column that has the M or K in it. Then the blank ones will be at the bottom of the list and you can delete them. Or you could filter the list based on that column and filter out the blanks.

Or, it would be pretty easy to write a VBA macro to do this...



john wrote:

list data without blank rows
29-Sep-09

I have a spreadsheet full of data. I want to organize my data on a second tab
by looking in a single column and if a text value is present (such as an "M"
or a "K"), then copy data in the same row to the second tab. But not every
row will have a text value, some may be blank and I don't want a bunch of
blank rows on my second tab sheet. Any ideas??

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx

 

[Max]

In say B2, array-entered:
Array-entered means that you got to press CTRL+SHIFT+ENTER (CSE) to confirm
the formula (instead of just pressing ENTER). Post confirmation, do eyeball
the formula bar, there should be curly braces: { .. } wrapped around the
formula. If there's none, then it isn't array-entered (and it won't work),
and you gotta click inside the formula bar and re-do the CSE stuff again.
Re-eyeball to confirm. Btw .. the earlier simpler way doesn't require any
array-entry.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[Max]

thankyou for the post, this works, but is there a way to do it without
creating column A?

The earlier response gives you the comfort of a simple, fast formula set-up
that works well, and is easy to understand, implement & cross-apply. Well
worth the price of using a single criteria column

Well, if you really are that tightly strapped for columns in your sheet ..
In Sheet2,
In say B2, array-entered:
=IF(ISERROR(SMALL(IF((ISTEXT(Sheet1!$B$2:$B$10))*(Sheet1!$B$2:$B$10<>""),ROW($B$2:$B$10)-ROW($B$2)+1),ROWS($1:1))),"",INDEX(Sheet1!A$2:A$10,SMALL(IF((ISTEXT(Sheet1!$B$2:$B$10))*(Sheet1!$B$2:$B$10<>""),ROW($B$2:$B$10)-ROW($B$2)+1),ROWS($1:1))))
Copy across to D2, fill down. You got to adapt the fixed ranges to suit the
expected extents. Any good? worth a YES hit now?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[john]

Max, this works great, thanks alot for your help. One more thing, can you
make it return just the row number that the data is taken from in another
column. Thanks again for spending your time on my problem. John.

 

[Max]

make it return just the row number that the data is taken from ..

Just remove the INDEX bit, and the adjustment from the earlier array
Array-enter this, copy down:
=IF(ISERROR(SMALL(IF((ISTEXT(Sheet1!$B$2:$B$10))*(Sheet1!$B$2:$B$10<>""),ROW($B$2:$B$10)),ROWS($1:1))),"",SMALL(IF((ISTEXT(Sheet1!$B$2:$B$10))*(Sheet1!$B$2:$B$10<>""),ROW($B$2:$B$10)),ROWS($1:1)))
The row numbers returned will be the actual row numbers
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik