Equals cell unless blank

Hi all,

-----A---------------B-----
1--Apple---------Apple
2-----------------Bannana
3--Bannana-----Orange
3--Orange-------Pear
4-----------------Kiwi
5-----------------Strawberry
6--Pear----------------------
7--Kiwi----------------------
8-----------------------------
9--Strawberry---------------


In column A I have a column of data that I'm trying to replicate into column B. I want to ignore the blank cells so only populated ones are copied into column B. Does anybody know how I can do this? Expected results in Column B.

**NOTE - I know a pivot table would remove the blanks but for this excercise I can't use a pivot table and needs to be formula based not VB

Many thanks in advance

Jason

Originally Posted by Special-K

@Punnam: Looks like the forum has corrupted your formula

This works

=IFERROR(INDEX(A$1:A$14,SMALL((IF(LEN(A$1:A$14),ROW(INDIRECT("1:"&ROWS(A$1:A$14))))),ROW(A1)),1),"")

Array formula use Ctrl-Shift-Enter

Source: http://www.cpearson.com/excel/noblanks.aspx

Cool, works perfectly.

If I want the range of cells to lookup to C13:C412 insted of A1:14, what do I need to change?

Tried this but didn't work...

=IFERROR(INDEX(C$13:C$412,SMALL((IF(LEN(C$13:C$412),ROW(INDIRECT("13:"&ROWS(C$13:C$412))))),ROW(C13)),3),"")

Thanks again!

Nevermind, got it...

=IFERROR(INDEX(C$13:C$26,SMALL((IF(LEN(C$13:C$26),ROW(INDIRECT("1:"&ROWS(C$13:C$26))))),ROW(A1)),1),"")


Thanks all!

@ Special -k

Thanks for giving me a new way of approach .

The formula i used is the attached workbook is the correct & works fine to retrieve the unique values in column ,one and the same is below

=IFERROR(INDEX($A$1:$A$11,MATCH(0,COUNTIF($B$1:B2,$A$1:$A$11),0)),"").

Punnam

@ jwillis07,

Kindly confirm me whether my post 2 is working for you ?

Punnam