Data Drop Down with Constent Break

[MMadni]

Dear All

I have query regarding data drop down, in my sheet I have 38K lines, and around 4000 material name but with break of multiples of line.

Like one material name come in 1st row and 20 rows are empty, then 2nd material name start and so on upto 38K line.

I wants to drop down (Fill material relevant name in blank cells) date in short time. Currently I am using Ctrl+D one by one, but it is take too much time.

kindly suggest any other good solution.

Regards

Muhammad Madni

[#2]

@MMadni 

If this is a one-time operation you could enter in C1:

=B1

Then in C2, enter:

=IF(ISBLANK(B2),C1,B2)

 and copy it all the way down. See attached for an example.

Once you are satisfied with the end result, you can Copy column C and Paste as Values in B and then delete column C.