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.

 

[SpillerBD]

Assuming your original begins in A1 including headers, use this formula in C2 and Fill Across and down.

=INDIRECT("R"&(ROW()-2)*4+COLUMN()-1&"C2",FALSE)

 

[moonsketcher]

Assuming your original begins in A1 including headers, use this formula in C2 and Fill Across and down.

=INDIRECT("R"&(ROW()-2)*4+COLUMN()-1&"C2",FALSE)

That works really great for the example data, so I'm partially there.

The problem is that the real data is not always exactly 4 rows long. Sometimes it could be 5 or 6 rows long. So in my perfect world where I am asking for the moon....I would need it to identify "Name" as the starting text to hunt for. Then auto fill from there. Hit the next name, drop down a line then auto fill.

I hope that makes sense.

And if I can't do it, so be it.

FYI, I cheered OUT LOUD and scared the crap out of my dogs when I tested it and it worked on my example data. So thank you very much.

 

[SpillerBD]

at work now but I have a concept in my brain to "flesh out" after work.
I'm in USA central time....

 

[SpillerBD]

...The problem is that the real data is not always exactly 4 rows long. Sometimes it could be 5 or 6 rows long. So in my perfect world where I am asking for the moon....I would need it to identify "Name" as the starting text to hunt for. Then auto fill from there. Hit the next name, drop down a line then auto fill.

Are the Header names always the same 4-6 item names?

 

[moonsketcher]

Yep, always the same header names. Just not necessarily always the same amount of fields.

Thank you so much for your help.

 

[SpillerBD]

Can you list the possible field names (or reasonable substitutes) ?

 

[SpillerBD]

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.)

 

[hiker95]

moonsketcher,

Welcome to the MrExcel forum.

Here is a macro solution for you to consider, that uses two arrays in memory, that will adjust to the varying number of raw data rows.

I assume that both worksheets exist, and, that both have titles in row 1.

You can change the worksheet names in the macro.

Sample worksheets before the macro:

Excel 2007
	A	B
1	Data Type	Large Column of Data
2	Name:	Mark
3	Address:	123 Roadname here
4	City/State:	Columbus, Ohio
5	Phone:	555.555.5555
6	Name:	John
7	Address:	456 Roadtest Rd
8	City/State:	San Diego, California
9	Not This1:	Not This1:
10	Phone:	555.555.5555
11	Name:	Tom
12	Address:	456 Testing Ct
13	Not This1:	Not This1:
14	City/State:	Seattle, Washington
15	Not This2:	Not This2:
16	Phone:	555.555.5555
17	Name:	Bob
18	Not This1:	Not This1:
19	Not This2:	Not This2:
20	Address:	123 Excel Way
21	Not This3:	Not This3:
22	City/State:	Castlerock, Maine
23	Phone:	555.555.5555
24
Sheet1

Excel 2007
	A	B	C	D
1	Name	Address	City/State	Phone
2
3
4
5
6
Sheet2

And, after the macro:

Excel 2007
	A	B	C	D
1	Name	Address	City/State	Phone
2	Mark	123 Roadname here	Columbus, Ohio	555.555.5555
3	John	456 Roadtest Rd	San Diego, California	555.555.5555
4	Tom	456 Testing Ct	Seattle, Washington	555.555.5555
5	Bob	123 Excel Way	Castlerock, Maine	555.555.5555
6
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorgData()
' hiker9, 06/08/2016, ME945802
Dim w1 As Worksheet, wr As Worksheet
Dim a As Variant, i As Long
Dim o As Variant, j As Long
Dim n As Long, nr As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")   '<-- you can change the sheet name here
Set wr = Sheets("Sheet2")   '<-- you can change the sheet name here
a = w1.Range("A2:B" & w1.Range("A" & Rows.Count).End(xlUp).Row)
n = Application.CountIf(w1.Columns(1), "Name:")
ReDim o(1 To n, 1 To 4)
For i = 1 To UBound(a, 1)
  Select Case a(i, 1)
    Case "Name:"
      j = j + 1: o(j, 1) = a(i, 2)
    Case "Address:"
      o(j, 2) = a(i, 2)
    Case "City/State:"
      o(j, 3) = a(i, 2)
    Case "Phone:"
      o(j, 4) = a(i, 2)
    Case Else
      'do nothing
  End Select
Next i
With wr
  nr = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
  .Cells(nr, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(1).Resize(, 4).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.

 

[moonsketcher]

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.