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.

Tip

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

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.