Return non blank list

[subatomic]

Hi,

does any1 know how to return all non-blank items from a row (cafe table2) to another row on another sheet (2)



i have worked very hard so far but not been able to do it. so far i have

=IFERROR(INDEX('Cafe Table'!B2:BQ2,SMALL(IF(LEN('Cafe Table'!B2:BQ2)>0,COLUMN('Cafe Table'!B2:BQ2)-COLUMN('Cafe Table'!$B$2)),COLUMN('Cafe Table'!1:1)),1),"")

the formula works for columns but i need to change it to rows and have been unsuccessful.

please please help.

i also need a formula tht returns the word in the cell above the nonblank cell in the row.


this is really important to me, i will help others with more simple stuff to pay back this community.

please please help me

 

[Jeffrey Mahoney]

Please try this:
I put this formula in Cell B4 on different sheet than Cafe Table
=IFERROR(INDEX('Cafe Table'!$B2:$BQ2,AGGREGATE(15,6,COLUMN('Cafe Table'!$B2:$BQ2)-COLUMN('Cafe Table'!$A$2)/(LEN('Cafe Table'!$B2:$BQ2)>0),COLUMNS($B$2:B2))),"")

The part in blue isn't to look at the dataset, but rather to count the columns of where you want the contiguous data to be. In other words, if you put this formula in column C and copy across, the address supplied will work. If you put the first formula in column K then it should read: $K2:K2

It makes the list contiguous.


Jeff

 

[subatomic]

Please try this:
I put this formula in Cell B4 on different sheet than Cafe Table
=IFERROR(INDEX('Cafe Table'!$B2:$BQ2,AGGREGATE(15,6,COLUMN('Cafe Table'!$B2:$BQ2)-COLUMN('Cafe Table'!$A$2)/(LEN('Cafe Table'!$B2:$BQ2)>0),COLUMNS($B$2:B2))),"")

The part in blue isn't to look at the dataset, but rather to count the columns of where you want the contiguous data to be. In other words, if you put this formula in column C and copy across, the address supplied will work. If you put the first formula in column K then it should read: $K2:K2

It makes the list contiguous.


Jeff

Many thanks Jeffrey!

Sweet work bro !

i was just about to come back and say i found something but it was messy. again much thanks, this is going to save my ***

 

[Jeffrey Mahoney]

You're welcome.

I made a small error in my explanation
This: if you put this formula in column C and copy across
Should be: if you put this formula in column B and copy across