# Excel – Combining multiple cells in a single row

Posted on

QUESTION :

I have data disposed in this manner:

``````Jonh Seedman                New York, New York             01/01/1900
5th Ave                     +1 NXX-NXX-XXXX
Homer Simpson               Springfield, Illinois.         02/01/1900
742 Evergreen Terrace       +1 NXX-NXX-XXXX
Peter Parker                New York, New York             03/01/1900
20 Ingram St                +1 NXX-NXX-XXXX
``````

I would like to hopefully get the following result using Excel (but not VBA):

``````    NAME.         BIRTH.             STATE.               ADDRESS.                    MOBILE
Jonh Seedman.   01/01/1900     New York, New York      5th Ave                   +1 NXX-NXX-XXXX
Homer Simpson   02/01/1900     Springfield, Illinois.  742 Evergreen Terrace     +1 NXX-NXX-XXXX
Peter Parker    03/01/1900     New York, New York      20 Ingram St              +1 NXX-NXX-XXXX
``````

Any hints will be more than welcome guys. Thank you to whoever will take the time.

With your original data in `A:C`, enter the column headers in `F1:J1` and then the following formulas:

``````F2: =IF(INDEX(\$A:\$A,(ROWS(\$1:1)-1)*2+1)="","",INDEX(\$A:\$A,(ROWS(\$1:1)-1)*2+1))
G2: =IF(INDEX(\$C:\$C,(ROWS(\$1:1)-1)*2+1)="","",INDEX(\$C:\$C,(ROWS(\$1:1)-1)*2+1))
H2: =IF(INDEX(\$B:\$B,(ROWS(\$1:1)-1)*2+1)="","",INDEX(\$B:\$B,(ROWS(\$1:1)-1)*2+1))
I2: =IF(INDEX(\$A:\$A,(ROWS(\$1:1)-1)*2+2)="","",INDEX(\$A:\$A,(ROWS(\$1:1)-1)*2+2))
J2: =IF(INDEX(\$B:\$B,(ROWS(\$1:1)-1)*2+2)="","",INDEX(\$B:\$B,(ROWS(\$1:1)-1)*2+2))
``````

then fill down until you start seeing blanks

Source Data

Processed data

If we assume that your data always falls in 3 columns and always spans 2 rows per record, then this would work…

1 – First create two helper columns.

``````Helper 1, alternate A & B on every other row. A formula can help you with this.

Helper 2, number your data as it currently is... 1, 2, 3, 4, etc.
``````

2 – Copy all your columns and paste in another sheet. Then order by Helper 1 and then Helper 2.

3 – Copy your Row “B” records and paste to the right of your row “A” records.

4 – Remove row B rows.

5 – Reorder your columns as you desire.