Function to copy-paste names in empty cells for a column

Arunakumaar · January 4, 2020, 7:07pm

I have a list of names under a column(over 200,000). But there are many empty cells between the names. I would like to fill those empty cells with the name that is available on the top. For example, check column A and column B in the file below. The original file has column A. I need values as in Column B. I know I can drag to copy-paste. But I have over 200,000 cells under the column Contact Name. It looks like it would take over 20 hours easily. Help is much appreciated:

Test File.ods

I have attached screenshots below:

anon73440385 · January 4, 2020, 7:52pm

Please make a short, descriptive title (as already asked to do in your last question by @Ratslinger ).

Arunakumaar · January 4, 2020, 8:18pm

Okay. I have edited the title. I hope this would be okay

anon73440385 · January 4, 2020, 11:45pm

I don’t understand the question. Could you please provide a short sample file - let’s says 20 or 30 rows, showing in column A the structure of the data you have and in column B how you want it to look like?

Please do not use Add Answer but edit your original question to enhance the details of your question and use the clip icon to attach a file. Thanks in advance …

Arunakumaar · January 5, 2020, 8:59am

I have added screenshots and a file. I hope this helps @anon73440385

Lupp · January 5, 2020, 12:02pm

(A very similar qustion was posted recently, but the subject was worded poorly, so I cannot find the thread now,
Some time ago there also was this thread concerning the situation.)

I would do it by formulas:
-0- If column B not is empty, insert an empty column there.
-1- Enter into cell B2 the formula =IF(A2="";B1;A2)
-2- Fill the formula down as far as needed.
-3- Depending on your needs probably Copy results and PasteSpecial (contents only, no formulas) to A1 (start).
-4- If done as described under -3- delete column B.

It can also be done with user code. Writing it was just for fun because I won’t use it, and because it mostly will not be very efficient. If you are interested nonetheless, you may look into this attached example where the code is contained in.

Arunakumaar · January 5, 2020, 12:56pm

I looked into the attached example file you have shared. I am unable to do this in my libre office. Rather I do not know how to implement this. A step by step process would really help. Thanks for sharing your answer.

Lupp · January 5, 2020, 6:27pm

Concerning the recommended solution by formulas I gave a step-by-step guide, and the example should be very clear. I’m afraid I cannot do it better by my means.

Concrning the “macro” soltion: If you are not at leas basically acquainted with macros, their libraries and modules, this i not the best example to start with. First of all: you can put both the routines into one module.

Being extremely curious about macros you should start here. The IDE info may at least help you to get the code to a place from where you can call it for any spreadsheet document you are working on.
(Anyway, I already told that the macro solution is not recommended. I did it myself to study a few ideas. It wasn’t, "developed"for actual use but for “academical interest”.)

Arunakumaar · January 5, 2020, 7:58pm

Hey. My friend assisted me with this. He used your solution. The thing is I do not know how to apply a formula. I did as you mentioned and was getting the message ‘Name?’ error. I have never copy-pasted formulas. I tried for 2 hours and could not do these simple steps… Especially step 2: Fill the formula down as far as needed( I dunno how to do this ). I will be learning from my friend when I meet him tomorrow. Thank you soo much!!