Regarding breaking of occuring data into rows of a table

Hi all,

As metion from the title, i have a little trouble regarding breaking down the data in a row.

Right now what the data i have is


-----A----------B----------C-------------D------------E-----------F----------G ------------H----------I ----------J---------K-|________|_________|__________|___________|___________|________|___________|___________|________|________| _________|
|----1 -----|--------| ---2 ----- |----------- | -----3 ------| ----------- |------1 ------|-------|------2------| -------| ------3 ------


to

-----A----------B----------C----
|________|_________|__________|
---1---- ---2---- ---3----
---1---- ---2---- ---3----
---1---- ---2---- ---3----

It will be better if you post a sample file so we can understand the layout better.

Roger ! =) , btw how do i upload an excel file ? new here sorry breakdatatest.JPG

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

So there wouldnt be headings in columns G to Q?

Are there chances of having more rows of data, like in row 4, 5, 6 etc?

Originally Posted by arlu1201

Are there chances of having more rows of data, like in row 4, 5, 6 etc?

yes ! i got the feeling i have to shift those rows down so that those data can be insert to the rows

Note that each row will have the similar reoccuring data but some might have a longer data pattern
for example

row 1 have 3 students as shown ,
row 2 will have 4 students

so the number of student data is varies

so the end product i am seek for advice is that

Row1 Data.row1
------Data.row1
------Data.row1
row2 data.row2
------data.row2
------data.row2
------data.row2
row3 data.row3
row4 data.row4
------data.row4

sorry for the long thread ,

just to add on to the data , regardin All row of data , each data has the first heading the same , maybe this could easier like search for every occuring CLass number then pull in into another row thats what i thought =)

Also if you would ask me how do i get this data layout , it is from email generated values, ( outlook 2010 message body with table ) it was at first in one long message in warp text in a single cell then i split my char(13) them using some vba codes and i end up here ! haha =)


below here are sample values :

Row 1 data : 1 james 12 male 1 lousi 13 male
row 2 data : 1 happy 13 male 1 john 14 male

Class		Name		Age		Gender
1		James		12		Male
1		Louis		13		Male

Class		Name		Age		Gender
1		Happy		12		Male
1		John		13		Male

Originally Posted by andywsw

sorry for the long thread ,

just to add on to the data , regardin All row of data , each data has the first heading the same , maybe this could easier like search for every occuring CLass number then pull in into another row thats what i thought =)

Also if you would ask me how do i get this data layout , it is from email generated values, ( outlook 2010 message body with table ) it was at first in one long message in warp text in a single cell then i split my char(13) them using some vba codes and i end up here ! haha =)


below here are sample values :

Row 1 data : 1 james 12 male 1 lousi 13 male
row 2 data : 1 happy 13 male 1 john 14 male

Class		Name		Age		Gender
1		James		12		Male
1		Louis		13		Male

Class		Name		Age		Gender
1		Happy		12		Male
1		John		13		Male

Originally Posted by jindon

Try the attached

Attachment 149605

@ jindon just a question Let say if i have 11 Headings ( the acutal ) rather than 4 , what am i suppose to change? and the following quote above regarding the first heading how do i add in ? Many Thanks =)

i did try some modification with the codes, what i did was changing all 4 to 11 ( which i assume was the number of headings) , i got an out of memory error

Originally Posted by jindon

You need to upload the file again with the desired results.

Sheet structure should be exact to the actual sheet. (dummy data should be OK)

i have attach to excat copy of how my lay out is please take alook and advice me on the marco/vba . Thanks alot !

THIS is the laterest version copy >>>>> breakdatatest3.xls <<<<<


* Do not take the below copy * V

hi ! i got a subscript out of range , any ideas ?


i tried on the dummy data , it works perfectly !

but when i shift over the codes to the actual data it gives that error ! ( using the same format layout as the dummy data )




also if it is possible that you can explain to me the codes that you wrote, there are some i had not explore before ! something new to learn from you =)



thanks

sorry to bother you = S

just a few questions

1) regarding this code would it be affected if there is more headings ( like new headings being added into the existing data)?

2) regarding the code i have problem understanding this line ReDim b(1 To UBound(a, 1) * UBound(a, 2) \ 11, 1 To UBound(a, 2)) on the bold part , why 11?

3) i realise that those empty column do contain some value but is not visible , this was result when i split the messages ( words are connect , no space in between words , like one long string of words )

using this code
'splits Text active cell using ALT+10 char as separator
Application.ScreenUpdating = False
Dim splitVals As Variant
Dim totalVals As Long
'split every row untill is empty
Worksheets("sheet2").Activate
Range("F2").Select
Do Until IsEmpty(ActiveCell.Value)
splitVals = Split(ActiveCell.Value, Chr(13))
totalVals = UBound(splitVals)
Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row, ActiveCell.Column + 1 + totalVals)).Value = splitVals
ActiveCell.Offset(1, 0).Select
Loop


Which resulted some spacing or value at the empty column , so how do i add on to your existing code to remove them or are they treated as empty column ?