formula that will copy column from 1 column 2 another -Blanks

[Beattle Juice]

would anybody have a formula that will copy say column B8:B1004 to C8:C1004 with out blanks there is a formula in the cell only want the seen value left in C column

is dynamic the right word for this B8:B1004 may change do to formula in its cell which will cause others to change and this is needed

[Ift Yewd Toadme]

try A=IFERROR(INDEX(B$8:B$1004,SMALL(IF(B$8:B$1004<>"",ROW(B$8:B$1004)-ROW(B$8)+1),ROWS(C$8:C8))),""

[Kevin UK]

@ Ift Yewd Toadme

You forgot to mention that your formula is a array formula, so it needs to be committed CTRL + SHIFT + ENTER and you need a closing bracket on the end, probably a typo.

@ Beattle Juice

For a non array version:

Code: Select all

=IFERROR(INDEX(B$8:B$15,AGGREGATE(15,6,ROW(B$8:B$15)-ROW(B$7)/(B$8:B$15<>""),ROWS(B$8:B8))),"")

[Beattle Juice]

yes thanks so much both i am getting closer to finishing this thing now ..if i can figure out a few more pieces i think i will be done with this thing

[Ift Yewd Toadme]

@ Kevin UK yeah think it was a copy paste error thank you
i cant seam to get this one to work am i doing something wrong


=IFERROR(INDEX(B$8:B$15,AGGREGATE(15,6,ROW(B$8:B$15)-ROW(B$7)/(B$8:B$15<>""),ROWS(B$8:B8))),"")
it copies list but dont take out blanks for me

[Kevin UK]

Hi Ift Yewd Toadme

The OP has Excel 2013, the aggregate function is only available in Excel 2010 and later versions. You have 2007 in your profile.