Merging two row cells and repeating down a column (not down a row).

[Benjamin Fork]

I'm familiar with, and found many help forums, on how to merge multiple cells in a row and repeat the process down a row. I.E. A1+A2, A3+A4, repeat. just by clicking and dragging. I'm asking how to merge rows down a column and repeat with a click and drag.

I am working on formatting an excel sheet for an ArcGIS application. I have over 7,000 rows of data. Here's the problem.

Set up/scenario:

Column A has rows 1 and 2 separated (A1, A2), rows 3 and 4 separated (A3, A4), etc none of the rows down are merged (remember 7,000 plus rows). 

Columns B-G have rows 1 and 2 merged (B1+B2), rows 3 and 4 merged (B3+B4), all the way down the columns.

Question: 

How do I get the cells in column A to match the merges in columns B-G and then click and drag the format or repeat it down the column so that A1+A2, A3+A4, etc down a single column (basically merging two rows over and over down a column.

I hope there's an answer here! Thanks for your time all!

[#2]

Dear Benajamin Fork,

Method 1

Step 1. Select Cells B1 to B7000

Step 2. Copy

Step 3. Select cell A1.

Step 4. Right click > Paste special > Formats

Method 2

Step 1. Select cells B1 to B7000.

Step 2. Select Format Painter

Step 3. Click cell A1. All Formats will get pasted.

Important Information:

In case, data is present in cells A1, A2, A3, A4..., then you may need to merge the data before you perform the operation of merging of cells.

One way to do this is by using a temporary helper column. The joining (merging) of data shall depend on the type of data. I am giving an example of text data. In case of numbers or other type of data, then you need to take adequate precautions.

For text data:

In cell C1, enter the formula =A1&" "&A2.

Select cells C1 and C2 and drag down the formula, till cell C7000.

Copy cells C1 to C7000.

In cell A1, Paste Special > Values.

The data from cells C1 to C7000 will get pasted in cells A1 to A7000.

Delete the data in cells C1 to C7000.

I hope this solves your problem.

Regards,

Vijaykumar Shetye,

Spreadsheet Excellence,

Panaji, Goa, India