# arrangement

#### sahil

##### Board Regular
I've vertically data in each cell like this.

A1 john
A2 sam
A3 robin
A4 xxx
A5 xxx
A6 yourself
A7 xxx
A8 no need

So on..... in very large amount.

But need substitution here.
I dont want "xxx".

I mean I need data in B row vertically but like this.

B1 john
B2 sam
B3 robin
B6 yourself
B8 no need

Please suggest any formula for the "B" cells.

Sahil.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### nbrcrunch

##### Well-known Member
No formula will hide cells. However, you can hide values. =IF(Cell="XXX","",Cell)

Where "cell's is the cell address such as A1, A2, etc.

Alternatley, I suggest you turn on Filtering.

Last edited:

#### Willem!

##### Board Regular
Hi!

Quick fix: select all your names, and sort them (e.g. alphabetically), in doing so you will sort all the blanks together.

#### Willem!

##### Board Regular
(BTW: if you want to keep/add the 'B#'-numbers... add them before you sort on the names, sort, remove the blanks, sort again in the B's and voila!

#### sahil

##### Board Regular
let me clear first.

Each cell has a formula so that the names and xxx inside the cell.

and for that reason I cannot sort. in order to sort I need to delete formulas and obviously I dont want to do that.

And can not filter as well because it just hide xxx. but I need remove all xxx.

because I have to make a list in which I need only names vertically. and I can get such list only if xxx remove.

Is there any way?

#### Kaps_mr2

##### Well-known Member
Try using a helper column -

B1 = IF(A1="XXX",1,0)

and then sort by the 1's. Thanks

Kaps

#### sahil

##### Board Regular
How do I sort sir ???????, if there is a formula in B1 to downward.

is there any way to sort cells that includes formula?

#### nbrcrunch

##### Well-known Member
Select all three columns (really, you could have just sorted on B) and then delete. You said you cannot sort on formulas. that is not true.

#### pgc01

##### MrExcel MVP
Hi

Not sure I understood correctly. Try in B1:

=IF(ROWS(\$B\$1:B1)>SUMPRODUCT(--(\$A\$1:\$A\$100<>""),--(\$A\$1:\$A\$100<>"xxx")),"",INDEX(\$A\$1:\$A\$100,SMALL(IF(\$A\$1:\$A\$100<>"",IF(\$A\$1:\$A\$100<>"xxx",ROW(\$A\$1:\$A\$100)-ROW(\$A\$1)+1)),ROWS(\$B\$1:B1))))

This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

Copy down

#### Kaps_mr2

##### Well-known Member
Try using a helper column -

B1 = IF(A1="XXX",1,0)

and then sort by the 1's. Thanks

Kaps

sorry I should have said copy down column B . thks

kaps

Replies
1
Views
129
Replies
3
Views
167
Replies
5
Views
311
Replies
12
Views
593
Replies
4
Views
176

1,191,134
Messages
5,984,864
Members
439,921
Latest member
Neocold

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