Converting similar bunches of data into columns

Rauday

New Member
Joined
Nov 5, 2005
Messages
4
Hello All

I have been given an excel file which contains similar groups of data positioned in one column.

i.e.
Name1
Address1
Phone Number1

Name2
Address2
Phone Number2

NameN
AddressN
Phone NumberN

I am trying to simply reposition into rows

i.e.

Name Address Phone#
Name1 Address1 Phone Number1
Name2 Address2 Phone Number2
Name3 Address3 Phone Number3


Thanks to all who reply
I appreciate your time on this

Regards

Rauday
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Assumptions:

1) Column A contains your data

2) A1 contains your label/header

3) Format is consistent (3 rows of data for each record, separated by a blank row)

Formula:

B2, copied across and down:

=OFFSET($A$2,((ROW()-ROW(B$2)+1)*4-4)+(COLUMN()-COLUMN($B2)),0)

Hope this helps!
 
Upvote 0
Domenic,

If there are not too many addresses, you can copy the Name, Address and Phone rows. Right click in a column where you want the information, select Paste Special > Select Transpose. You would have to do this for set of Name, Address and Phone information, however it gives you control to get the information in rows without spaces between rows.

Mike
 
Upvote 0
mdavidge said:
Domenic,

If there are not too many addresses, you can copy the Name, Address and Phone rows. Right click in a column where you want the information, select Paste Special > Select Transpose. You would have to do this for set of Name, Address and Phone information, however it gives you control to get the information in rows without spaces between rows.

Mike

Actually, if you try my solution, you'll see that the result won't include blank rows. The last assumption regarding the format being consistent had to do with the source data. My solution will provide the OP with the desired result as long as there's three rows of data for each record, and each record is separated by a blank row.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top