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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,402
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
19,402
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,055
Messages
5,569,948
Members
412,299
Latest member
agentless
Top