Excel – Combining multiple cells in a single row

Posted on


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
enter image description here

Processed data
enter image description here

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. 

enter image description here

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.

enter image description here

5 – Reorder your columns as you desire.

Leave a Reply

Your email address will not be published. Required fields are marked *