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.
ANSWER :
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.