how do you do this?

littledrew

New Member
Joined
Feb 11, 2005
Messages
2
lets say i have a single column group of cells... theres 20 cells and most of them are blank and a few have names.

how could i use index/array or whatever function to relist those names ( and not the blank spaces) in another area ( for aesthetic purposes) ??

thanks
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Alisa

Board Regular
Joined
Jan 28, 2005
Messages
97
copy the range
then paste special and check the box "skip blanks"
if you are using it in a macro, its:

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= True
 

Bubbis Thedog

Well-known Member
Joined
Jul 29, 2004
Messages
967
You could select the upper cell in the range, select Data-->Filter-->Autofilter. Then you could select NonBlanks from the list.

If this is not sufficient, tell us.

Bubbis
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
littledrew said:
lets say i have a single column group of cells... theres 20 cells and most of them are blank and a few have names.

how could i use index/array or whatever function to relist those names ( and not the blank spaces) in another area ( for aesthetic purposes) ??

thanks

If you insist on using a formula system...

Let A2:A23 house the data, including a label in A2.

In B2 enter: 0

which is needed.

In B3 enter & copy down:

=IF((A3<>""),LOOKUP(9.99999999999999E+307,$B$2:B2)+1,"")

In C1 enter:

=LOOKUP(9.99999999999999E+307,B3:B23)

In C2 enter: New List

In C3 enter & copy down:

=IF(ROW()-ROW(C$3)+1<=$C$1,LOOKUP(ROW()-ROW(C$3)+1,$B$3:$B$23,$A$3:$A$23),"")
 

littledrew

New Member
Joined
Feb 11, 2005
Messages
2
wow... that worked perfectly...wow...i'll try to analyze the code myself... thanks, this great :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top