Copy from column to column without blanks but with a twist

Hi guys

I am using the following simple formula to try and copy data from one column to another in the same spread sheet but blank cells are involved and I want the formula to bypass these

Column H – range H13:H2500 – is used for a product code
Column K – range K13:K2500 – is used for an edition code
There will be instances where column H will contain a value but the corresponding cell in column K may not e.g. IF H13 contains a value then K13 may or may not contain a value
IF a column H cell is blank however, the corresponding cell in column K will always be blank
NOTE: There are no pre-existing formulas in either of these columns’ cells.

Column AI – range AI13:AI2500 – is where I want to copy Column K cells to BUT based on the following argument:

Formula is conditional on the H cell containing a value i.e.:
IF the H cell contains a value then copy the value in the corresponding K cell, to the corresponding AI cell……..EVEN if the K cell is blank
BUT, IF the H cell is blank then do nothing
That is, I don't want column AI to have actual blanks

H13 – 12345 K13 – 8 AI – 8
H14 – 67890 K14 - AI -
H15 – 98765 K15 – 4 AI – 4
H16 - K16 - AI – 7
H17 - K17 - AI -
H18 – 65432 K18 – 7
H19 – 81234 K19 -

Hope this is clear enough for someone to help me out with an amended formula

Cheers

Hi,

You would get better help if you attach a sample workbook with enough data to demonstrate your requirement. Make sure your desired results are shown, mock them up manually if necessary. Remember to desensitize the file by removing all confidential information before upload!

See the following URL for help on how to upload a file.
http://www.excelforum.com/members/da...ch-a-file.html

Hi

Thanks so much for your prompt response, I amended the formula to reflect the ranges so it looks like this:

AI13=IFERROR(INDEX($K$13:$K$19,SMALL(IF($H$13:$H$19<>"",ROW($H$13:$H$19)-12),ROWS($A$1:A1))),"")

Where AI13 is the first destination cell
K is the values I want to copy
And H is the pre condition

However,it appears I've not done something because when I try the Ctrl Shift Enter it just flashes and won't apply the array brackets?

Cheers

I should have pointed out i don't get any sort of error it just doesn't apply the brackets?

Nah all good I made a blue
Thanks so much I have added reputation...........so fast so damn clever............I'm learning but seems that light at the end of the tunnel don't seem to be gettin any closer!!!