Transposing a large data set in a single column to multiple rows

[moonsketcher]

Hello

I have a long single column of data, that I need to transpose and have each new section separated onto a new row. (I realize that description is really confusing, I have included an example below)

Data Type	Large Column of Data
Name:	Mark
Address:	123 Roadname here
City/State:	Columbus, Ohio
Phone:	555.555.5555
Name:	John
Address:	456 Roadtest Rd
City/State:	San Diego, California
Phone:	555.555.5555
Name:	Tom
Address:	456 Testing Ct
City/State:	Seattle, Washington
Phone:	555.555.5555
Name:	Bob
Address:	123 Excel Way
City/State:	Castlerock, Maine
Phone:	555.555.5555

<tbody>
</tbody>

And I'm trying to set a template so that the data (when pasted into the sheet) is auto formatted in a separate sheet to look like this:

Name	Address	City/State	Phone
Mark	123 Roadname here	Columbus, Ohio	555.555.5555
John	456 Roadtest Rd	San Diego, California	555.555.5555
Tom	456 Testing Ct	Seattle, Washington	555.555.5555
Bob	123 Excel Way	Castlerock, Maine	555.555.5555

<tbody>
</tbody>

Obviously, this is false data. The data I'm working with is sensitive so I can't paste it here.

I have the distinct feeling I am overthinking it, but after days of beating my head against the wall, I am finally giving in and asking for help. I have hunted all over the web and this site, but I think in the process of overthinking - that I am missing some key search phrase to have the magical answer handed to me.

The =Transpose function will not work because it requires extra key strokes. And no break for the next row.

I am making a simple template for EU to utilize to paste in data and have it automatically formatted accordingly.

Any help that anyone could offer would be greatly appreciated.

Please and thank you in advance.

 

[moonsketcher]

Again, assuming your source data with headers begins in Cell A1.
Copy the possible Headers in C1:H1
In C2 paste the following formula

=IF($A2=$A$2,VLOOKUP(C$1,$A2:$B8,2,FALSE),"")

Fill Across and Down.

Makes the assumption there are a minimum of 4 fields per name and no more than 6 fields per name.

(Okay, so I took lunch at my desk.)

Lunch at your desk is never a good thing.

I pasted the formulate into C2 (as before) and it filled the first row but nothing further.

SO CLOSE!

I really can't tell you how much I appreciate your help.

 

[hiker95]

Sadly, I can't use a macro for this. I need to be able to ship it to EU and macro enabled docs get stripped out. But thank you very much for your help, I did save this for reference sakes.

moonsketcher,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[SpillerBD]

How many rows did you fill down?
Double check the $ in your formula....

 

[Mike Szczesny]

Can you use something like this. Hope this helps.
Mike Szczesny

Excel 2012
	A	B	C	D	E	F	G
1	name	mark		name	address	city	state
2	address	123 Jones		mark	123 Jones	houston	texas
3	city	houston		bill	456 jones	dallas	texas
4	state	texas		jack	789 mills	chicago	illinois
5	name	bill		dave	458 oak	boise	Idaho
6	address	456 jones
7	city	dallas
8	state	texas
9	name	jack
10	address	789 mills
11	city	chicago
12	state	illinois
13	name	dave
14	address	458 oak
15	city	boise
16	state	Idaho
Sheet1

Cell Formulas
Range	Formula
D2	{=IFERROR(INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=D$1,ROW($A$1:$A$16)-ROW($A$1)+1),ROWS($D$2:D2)))," ")}
E2	{=IFERROR(INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=E$1,ROW($A$1:$A$16)-ROW($A$1)+1),ROWS($D$2:E2)))," ")}
F2	{=IFERROR(INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=F$1,ROW($A$1:$A$16)-ROW($A$1)+1),ROWS($D$2:F2)))," ")}
G2	{=IFERROR(INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=G$1,ROW($A$1:$A$16)-ROW($A$1)+1),ROWS($D$2:G2)))," ")}
Press CTRL+SHIFT+ENTER to enter array formulas.