VBA unstructured data into structured please help

baleshst

Board Regular
Joined
Jan 24, 2010
Messages
131
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)
NameJames54Kelly111Fenny1
(The way it should be)
NameJames54
NameKelly111
NameFenny1

<tbody>
</tbody>
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
Code:
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
 
Upvote 0
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
Code:
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.
 
Upvote 0
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.
Code:
[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]
 
Upvote 0
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.
Code:
[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
 
Upvote 0
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...
Code:
[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).
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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
Back
Top