Consolidate data?

[zlk2000]

Guys

I am sure you all can help me...

Say I have data in a1, a3, a7, a10, a13 but I want all of the data in those cells to be consolidated and show up in e1, e2, e3, e4, e5 and so forth... so that they are consolidated without blanks, how can I do this without using the data sort and unchecking blank option. Id like to do this automatically by a function somehow.


any thoughts??

[JohnTopley]

In D1

=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER($B$1:$B$15),ROW($A$1:$A$15),""),ROWS($1:1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy to E then copy down.

[samba_ravi]

d1=IFERROR(INDEX(A:A,SMALL(INDEX(($B$1:$B$100="")*10^10+ROW($B$1:$B$100),0),ROWS(D$1:D1))),"")
try this and copy across

[zlk2000]

AWESOMEEEEEEEEEEEEEEE!! Man that is cool.Thank you very much for the help. Now I just gotta figure out what it all means so i can learn the process. very very helpful

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.