Fill blank range with corresponding values..

[kay007]

Dear experts,

i am currently working on a report that requires that i fill up with the range before i do vlookup or any other calculation(s).

My data is currently scattered and i would love to fill up the blank spaces with the corresponding values. See attached file.

e.g. the value for test B under column C is on row 6 while the rows 2 - 5 are blank etc.


Many thanks for your time!

[Kaper]

Proposition with formula:
In say H2 array formula:

Please Login or Register  to view this content.

and copy it down/right once calculated copy all results and paste special as values into C2


*) confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[kay007]

Thanks Kaper for your prompt response.. Please i 2 questions for you to help me with.

How do i make the formula dynamic so that it can extend to any row (currently its at 21). My actual data is in the 15k range and it can vary on monthly basis?
How do i include this formula in a vba code? My past experience .FormulaArray was not too pleasant and did not work?

Any pointer will be much appreciated.

Regards,
Kay

[Kaper]

With 15k rows it will be slow or very slow, but shall work too. Once it finished and you copied the results, you can of course delete whole range with formula.

[kay007]

Hi Kaper!

I tried that formula on a 50k data and it was extremely slooww!. I had to terminate it in order to do other things.

[JOHN H. DAVIS]

Does this help?

Please Login or Register  to view this content.

[kay007]

Thanks John. That works as well though slow as well but not as slow as the formula one.

However, i got an error when the code get to the tail end. See attached screen shot.

Note: I modified the range to suit my sheet

Error.JPG

Many thanks

[kay007]

Hi John,

I understood what is causing the error and so I just wrote a line to re-sort the data instead of deleting the inserted rows.

Many thanks for your time.. Much appreciated.

[JOHN H. DAVIS]

You're welcome. My mistake glad you worked it out and thanks for the feedback.

[Kaper]

Hm,
somehow my post from yesterday afternoon disappeared. As my code is a bit different, I post it here anyway.
It works the same way, I'd do it manually - sort on columns A, B and one of C,D,....
select empty cells in column C, D, ... fill them with =cellabove formula (commited with Ctrl+Enter) copy and paste as values, do the same for next column,...

Not tested, but may be it will be faster?
For sure would be good idea to add

Please Login or Register  to view this content.

at the beginning of the code

Please Login or Register  to view this content.