How to copy select cells and paste identically in another column?

**perfection** · 10-17-2010, 03:06 AM

I have a column AC (resulting from processing from AA) the orginal data entry column) that has the following values

AC1 2
AC2 55
AC3 4
AC4 #value!
AC5 75
AC6 8
AC7 #value!

i need to select all numerical values only which i do by Go to and selecting constants and numbers. This is then copied to clipboard

i would want to know how to paste this over the original column AA such that the values land up in AA1, AA2, AA3, AA5 and AA6 but the original values of AA4 and AA7 remain the same.

When I do so the copied values overwrite all the existing ones and produce a shorter column as all values were not selected

**DonkeyOte** · 10-17-2010, 03:11 AM

Reverse the logic - run GoTo -> Special on the Column but select Errors (& OK) - once selected press Del key to clear contents.

**outofthehat** · 10-17-2010, 03:15 AM

I think I'd be inclined to generate a column that gives you the full set of values you want first. In 2007 or later, the IFERROR() function is convenient.
So, in cell AD1:
=IFERROR(AC1,AA1)
This takes the value in AC1 unless it is an error, in which case it takes AA1.
Copy that down, and you should have a column with the numbers you want. Of course, you could wrap the IFERROR() function around whatever you have in column AC, to save on columns...

If using 2003 or earlier, you'll need the slightly longer
=IF(ISERROR(AC1),AA1,AC1)

**perfection** · 10-17-2010, 03:27 AM

Thanks DonkeyOte
But when i do that the errors get deleted and then copying and pasting the column values cause blanks in AA4 and AA7 whose values i want to retain