Changing cell contents

[lar]

Hello,
I am using Office for Macs 2011. Please see attachment for example.
I need to replace the names given in column B with the number given in column A (ie. change columns AB to look like columns DE). Problem is that there are over 300,000 rows in the file, so I cannot do it by hand. Is there a quick way to do this in excel?

Thank you in advance!

[JBeaucaire]

You don't really need a macro:

1) In B1, put this formula:

=IF($A2="", LOOKUP(99^99, $A$2:$A2), "")

2) Copy B1 down the data set

3) Copy column B, then Edit > Paste Special > Values to remove the formulas.



Here's a macro version of those same steps:

Please Login or Register  to view this content.

[lar]

Hi JB,
Thanks for the quick response. It is working so far great. A quick question though, is there a way to apply the formula to all of the rows in column B without having to pull down the formula from the cell above? I have over 300,000 cells, so this will take a long time. Because there are blank rows, I cannot just double click to auto fill...
thanks

[JBeaucaire]

Try the macro.