MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Merge Rows Quickly

November 22, 2001 - by Bill Jelen

Melinda writes:

Is there any way to merge two rows together quickly? The idea is to fill in the blank fields in row A with the information contained in row B.

I will soon have about 400 spreadsheets with up to 6000 rows each in which I will have to apply the above process. And of course this has to be done in a three day turn-around period.

Can you help??????

Yes. You need to highlight all 6000 rows. Edit - GoTo. Click the Special button. Click Blanks and then OK.

You now have all of the blanks selected. You are going to enter a formula to copy the value from the cell above. Type the equals sign, hit the up arrow, then hit Ctrl + Enter to enter this formula in all of the blank cells.

Finally, reselect all 6000 rows. Edit - Copy, Edit - PasteSpecial - Values - OK.


This is documented in one of the early tips of the week - Tip004

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point 2nd Edition

Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better. The chapter on the logic behind arrays has been expanded.