# Return non blank list

#### subatomic

##### New Member
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.

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.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Jeffrey Mahoney

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

Replies
3
Views
247
Replies
3
Views
180
Replies
7
Views
305
Replies
3
Views
796
Replies
8
Views
183

1,195,671
Messages
6,011,075
Members
441,581
Latest member
rp4717

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back