Arrange data

Dear Members

Can you please come up with a formula if possible to drag the red codes into the yello box?(please see attached sample)

This data is just a sample from a huge download and i have to do it manually which is very time consuming.

If you do come up with a macros please list each step for me as i do not have any experience in VBA whatsoever.

Many Thanks

If you wanted to do this manually (but quickly) ...

--> Highlight B2 to last row of data
--> Press F5 --> Choose Blanks --> Click OK
--> Now type in following formula: =IF(ISNUMBER(A3),A1,"") and press CTRL + ENTER to confirm entry

Hi DonkeyOte

Many thanks for your reply, however it did not quite work for me(please see the new attachment)

Regards

There is a leading space in the formula, ie before the = sign - run again but this time without the space before the = operator.

(the steps were perhaps not crystal clear)

Hi DonkeyOte

Thanks again, i tried it and it worked. I just got one more favour if possible. Is it possible that once i drag the red codes to yellow box they turn to bold.. then the red code is it automatically deleted because at the moment it is copying the codes and this result into duplicates which will be confusing

Many thanks

VBA would be the most logical route in truth

Please Login or Register  to view this content.

The principles in the above are the same - the code simply iterates the blanks in Col B and establishes whether or not to pull the prior value in the column to the left based on the value of the cell below in the column to the left.

The code, unlike the earlier approach, will apply formatting to the copied values and clear the originals

(If you have any event based code running in your file you should disable events etc...)

Hi Donkey Ote

Many thanks again, since i am new to VBA i'll try it once i know how to use the macros. I am sure it will work since i consider you an expert if not a guru given the number of people you helped.

Can you please suggest any website or books where i can start to learn about VBA and Macros from scratch. Something like an idiot guide to..

Many thanks

Originally Posted by ordinateur

Can you please suggest any website or books where i can start to learn about VBA and Macros from scratch. Something like an idiot guide to..

I think you'll find quite a few threads on this topic recommending books etc... I can't recommend anything personally I'm afraid - for my sins what I've picked up is the result of trial and error, macro recorder and forums like this... mainly trial & error.