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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,222
Office Version
  1. 365
Platform
  1. Windows
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!
 

mdavidge

Board Regular
Joined
Oct 14, 2005
Messages
87
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
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,222
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,993
Messages
5,834,772
Members
430,320
Latest member
Napzz

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
Top