VBA unstructured data into structured please help

[baleshst]

I have data source which is unstructured. Request you to help me in creating a <acronym title="visual basic for applications">vba</acronym> code which would structure the data.

(unstructured data)
	Name	James	5	4	Kelly	1	1	1	Fenny	1
(The way it should be)
	Name	James	5	4
	Name	Kelly	1	1	1
	Name	Fenny	1

<tbody>
</tbody>

 

[baitmaster]

This will do it. Don't use it on your original file without backing up / testing it. I've used an arbitrary range which happens to be where I pasted your example data - change this range to suit your needs

Sub restructure()
Dim rngMyData As Range: Set rngMyData = Range("E8:N8")
Dim cl As Range, i As Integer, j As Integer
For Each cl In rngMyData
    If cl = "" Then Exit Sub
    
    j = j + 1
    rngMyData.Cells(i + 2, j) = cl
    
    If IsNumeric(cl) And Not (IsNumeric(cl.Offset(0, 1))) Then
        i = i + 1
        j = 1
    End If
Next cl
End Sub

 

[baleshst]

This will do it. Don't use it on your original file without backing up / testing it. I've used an arbitrary range which happens to be where I pasted your example data - change this range to suit your needs

Sub restructure()
Dim rngMyData As Range: Set rngMyData = Range("E8:N8")
Dim cl As Range, i As Integer, j As Integer
For Each cl In rngMyData
    If cl = "" Then Exit Sub
    
    j = j + 1
    rngMyData.Cells(i + 2, j) = cl
    
    If IsNumeric(cl) And Not (IsNumeric(cl.Offset(0, 1))) Then
        i = i + 1
        j = 1
    End If
Next cl
End Sub

Thank you,

Just one addition, as seen above the word "Name" should report itself next to each row created.

 

[Rick Rothstein]

Here is my take on a solution for your question. Assuming the data you showed us started in cell A1, the following macro will put the restructured data in the cells below it.

[table="width: 500"]
[tr]
	[td]Sub Restructure()
  Dim X As Long, Data As Variant, Lines As Variant
  Data = Range("B1", Cells(1, Columns.Count).End(xlToLeft))
  For X = 1 To UBound(Data, 2)
    If Data(1, X) Like "[A-Za-z]*" Then Data(1, X) = vbLf & "Name" & Chr(1) & Data(1, X)
  Next
  Lines = Split(Join(Application.Index(Data, 1, 0), Chr(1)), vbLf)
  Range("A2").Resize(UBound(Lines) + 1) = Application.Transpose(Lines)
  Range("A2").Resize(UBound(Lines) + 1).TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)
End Sub[/td]
[/tr]
[/table]

 

[baleshst]

Here is my take on a solution for your question. Assuming the data you showed us started in cell A1, the following macro will put the restructured data in the cells below it.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Restructure()
  Dim X As Long, Data As Variant, Lines As Variant
  Data = Range("B1", Cells(1, Columns.Count).End(xlToLeft))
  For X = 1 To UBound(Data, 2)
    If Data(1, X) Like "[A-Za-z]*" Then Data(1, X) = vbLf & "Name" & Chr(1) & Data(1, X)
  Next
  Lines = Split(Join(Application.Index(Data, 1, 0), Chr(1)), vbLf)
  Range("A2").Resize(UBound(Lines) + 1) = Application.Transpose(Lines)
  Range("A2").Resize(UBound(Lines) + 1).TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the reply Rick
Your code is working fine

Can this code be adjusted to include last name also as seen below

(unstructured data)
Name James smith 5 4 Kelly Phillips 1 1 1 Fenny Rogers 1



(The way it should be)
Name James smith 5 4
Name Kelly Phillips 1 1 1
Name Fenny Rogers 1

 

[Rick Rothstein]

Thanks for the reply Rick
Your code is working fine

Can this code be adjusted to include last name also as seen below

(unstructured data)
Name James smith 5 4 Kelly Phillips 1 1 1 Fenny Rogers 1

(The way it should be)
Name James smith 5 4
Name Kelly Phillips 1 1 1
Name Fenny Rogers 1

First, let me start off by saying that you will almost never get the answer you need by not telling us about all of the data you have to process (see my longer version of this idea, which I have posted in the past, at the bottom of this message). With that said, give this revision to my macro a try...

[table="width: 500"]
[tr]
	[td]Sub Restructure()
  Dim X As Long, Data As Variant, Lines As Variant
  Data = Range("B1", Cells(1, Columns.Count).End(xlToLeft).Offset(, 1))
  For X = 1 To UBound(Data, 2) - 1
    If Data(1, X) Like "*[A-Za-z]*" And Data(1, X + 1) Like "*[A-Za-z]*" Then Data(1, X) = vbLf & "Name" & Chr(1) & Data(1, X)
  Next
  Lines = Split(Join(Application.Index(Data, 1, 0), Chr(1)), vbLf)
  Range("A2").Resize(UBound(Lines) + 1) = Application.Transpose(Lines)
  Range("A2").Resize(UBound(Lines) + 1).TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)
End Sub[/td]
[/tr]
[/table]

Please Note
-------------------
For future questions you may ask, please do not simplify your question for us... doing so will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[baleshst]

Thanks Rick really appreciate your help.
Will certainly work on your advise.