collapsing a list with empty cells

i have a list that is one hundred rows long but each column has only 8 or 10 elements in it.

i want to collapse the list to the length of the longest column of entries so it will end up being about 10 rows and bringing all the entries into those 10 rows. they do not have to keep relative alignment re rows, only columns.

i can think of some clumsy ways to do this but i'm looking for a clever quick way.

attached a sample. no i didn't. when i click on manage attachments this opens another window with nothing in it. don't know if the forum doesn't like my version of
firefox or what.

is there a way to paste a table into a post. it is relatively simple material without formulas.

thanx.

brian

1st 3 steps went just as advertised. but it only selects cells outside the table i pasted meaning that somehow, even thought they appear empty the cells are not BLANK.

the forumula that populated this table inserted "" for an empty cell, i.e. a text string with nothing in it. Maybe that is not interpreted as BLANK.

This was part of an IF function so maybe I can go back and put =BLANK() or something like that as the alternative value for the if funciton and then your method will work. . i'll try that, but if you or anyone have any ideas on how to get empty cells to respond to the BLANK query will appreciate it.

thanks,

brian

Tested with the ISBLANK() function and the cells are not blank. i can't figure out what to use for an alternative return in my if function that results in a blank cell. i have copied the cells and pasted special as values only but they are still not blank even without the formulas. Nothing shows in the formula bar when i click on one of these cells after pasting values only.

tried using various forms of BLANK() or =BLANK() as the return from one path of the IF function but get a NAME# error.

thanks for hopefully telling me something obvious i should have thought of.
frustrated in Rhode island

brian

so i have a number of files that could be simplified this way. I could write some kind of subroutine in xml that could ask for the number of rows and could jump through them and if a value was greater than 0 transfer copy that value and paste it in a new array keeping track of how many values were in the array already. I just thought their might be some quick way to do this and the special selection of blanks seems appropos but i can't figure out how to make the empty cells register as blank?

i'll see if i can do a sample file when i get home. I was clickon on "manage attachments". it opened another window but that window never got any content. i did it on this computer and i get thumbnail list of other attachments i have made in previous posts. so i'm assuming that the version of foxfire i'm running on my home computer doesn't talk to this function correctly. i kind of wish that simple stuff that used to work (all those existing uploaded files were uploaded from my home computer using the version of foxfire i still have) wasn't made to not work in order to accomodate new versions of who knows what, flash.

still looking for how to blank as cell from an internal formula.

thanks,

brian

got the attachment to work.

this should give you a general idea what i'm wotking with.

brian

Hi,

Just an alternative
You can even try another Array function also in Sheet1 (2)

Punnam

got it. can you help me to understand how your formula for cells in the helper column:

=COUNT(OFFSET($A$1,0,LOOKUP(10^10,B2:P2,$B$1:$P$1),ROW(A2),1))

is accomplishing the row labeling that enables pivot table to do the job.

obviously what it does is count occurences of entries in a column up to the point of entry in the row being labeled. that's beautiful. even though i know what it does,it isn't reverse engineering quickly in my mind.


thanks,

brian