Find non-blank cells and retrieve data at corresponding cells

**guochingsu** · 11-17-2012, 11:41 AM

Hello,

I tried to wriet an array formula as follows:

=INDEX(A:A,SMALL(IF(MATCH(TRUE,LEN(I$1:I$15000)<>0,0),ROW(I$1:I$15000)),ROW(A2)))

Basically I have data stored at columns from A column to I column, and I would like to search non-blank cells at column I and retrieve data at corresponding cells at A column and put those matched data at K column. For example, if I15 is a non-blank cell, then data at cell A15 will be retrieved and placed at K column.

Anything wrong with the above formula? thanks for the help..

**Moo the Dog** · 11-17-2012, 12:22 PM

You could try this VBA... an array formula with that many cells will be slow. Try this (obviously on test data first!):

Sub CopyA2K()
Dim thisRow As Long
For thisRow = 1 To 15000 'Change these numbers to match the range of rows you need changed.
    If Range("I" & thisRow).Value <> "" Then
        Range("K" & thisRow).Value = Range("A" & thisRow).Value
    End If
Next
End Sub

- Moo