How do i transpose vertical data into horizontal data

I have a load of data that has been pulled into an excel file, but the data has been preformatted into recurring rows. It is easier to explain when viewing the attached spreadsheet. But simply all i want to do is take the first set of titles in column A (30 rows) and transpose them to horizontal format (which i can do using the transpose function), and then ensure that all related data is added to the rows beneath each correct title.


Example-imported-from-text-file.xlsx
bdoshukAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Are these at regular intervals or irregular intervals?
0
bdoshukAuthor Commented:
there are irregular gaps ranging from between 3 -5 rows
0
loopfinityCommented:
I you are experienced with matlab you can tranpoze it in a minute just read data from the file
than transpoze it and write excel again.

I have no experince excel and other MS office applications.

regards.
0
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

bdoshukAuthor Commented:
I'm sorry but if this question is posted as Excel and you have no experience with MS office applications or excel, then why have you wasted your time and mine by answering?
0
Saqib Husain, SyedEngineerCommented:
Enter this formula in C1 and copy it down
=SMALL(IF($A$1:$A$97="Title",ROW($A$1:$A$97),9999999999),ROW())

Enter this formula in D1 and copy it down and across
=IF(OFFSET($A$1,$C1+COLUMN()-5,IF(ROW()=1,0,1))="","",OFFSET($A$1,$C1+COLUMN()-5,IF(ROW()=1,0,1)))

Saqib
0
bdoshukAuthor Commented:
I get lots of NUM errors see attached.
Example-imported-from-text-file.xlsx
0
Saqib Husain, SyedEngineerCommented:
Sorry my fault. The first formula is an array formula and has to be entered by pressing ctrl-shift-enter

Now go to C1 and press F2 and then press ctrl-shift-enter

then copy the formula down by dragging the tiny square at the bottom right corner of cell C1

Saqib
0
DaveCommented:
This code will do it on the seocnd sheet leavng your first sheet intact as is

Sample file with working code attached

Cheers

Dave
Sub CreateCSV_Output()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lngRow As Long
    Dim rng2 As Range
    Set ws1 = Sheets(1)
    Set ws2 = Sheets(2)
    Dim rng1 As Range
    Set rng1 = ws1.Columns("A").SpecialCells(xlConstants)
    With Application
        .ScreenUpdating
    End With
    ws2.Cells.C = False
    learContents
    For lngRow = 1 To rng1.Cells.Count / 30
        Set rng2 = Range(ws1.[a1], ws1.[a30])
        ws1.Range(ws1.[a1], ws1.[a30]).Offset(30 * (lngRow - 1), 1).Copy
        ws2.Cells(lngRow + 1, 1).PasteSpecial Transpose:=True
    Next lngRow
    ws1.Range(ws1.[a1], ws1.[a30]).Copy
    ws2.[a1].PasteSpecial Transpose:=True
    With Application
        .ScreenUpdating = True
        .CutCopyMode = False
    End With
End Sub

Open in new window

trans.xlsm
0
bdoshukAuthor Commented:
Thank you for this, but i think something is missing, when i run the button i get errors in the code, so i thought i would follow your advice and copy the formula manually which works for the first series. Is it possible to expand this by creating named ranges so that if I were to get larger text imports of the same nature (IE 100000 rows plus) it will be able to handle it? The file i originally supplied had to have the original data taken out, the original file, however has over 170,000 rows plus in a similar random format.
0
Saqib Husain, SyedEngineerCommented:
0
DaveCommented:
Bad copy and paste on my code, I killed part of a line

pls replace with this

Cheers

Dave
Sub CreateCSV_Output()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lngRow As Long
    Dim rng2 As Range
    Set ws1 = Sheets(1)
    Set ws2 = Sheets(2)
    Dim rng1 As Range
    Set rng1 = ws1.Columns("A").SpecialCells(xlConstants)
    With Application
        .ScreenUpdating = False
    End With
    ws2.UsedRange.ClearContents
    For lngRow = 1 To rng1.Cells.Count / 30
        Set rng2 = Range(ws1.[a1], ws1.[a30])
        ws1.Range(ws1.[a1], ws1.[a30]).Offset(30 * (lngRow - 1), 1).Copy
        ws2.Cells(lngRow + 1, 1).PasteSpecial Transpose:=True
    Next lngRow
    ws1.Range(ws1.[a1], ws1.[a30]).Copy
    ws2.[a1].PasteSpecial Transpose:=True
    With Application
        .ScreenUpdating = True
        .CutCopyMode = False
    End With
End Sub

Open in new window

trans.xlsm
0
bdoshukAuthor Commented:
Thanks Dave. This works great. Will it account for extra lines?? My original import is over 250000 rows, i reduced it for the purposes of uploading to the site and to show an example.
0
DaveCommented:
A bad night - my code hasn't adjusted properly for the blanks in the SpecialCells

I will adjust unless Saqib's solution has finished this

Cheers

Dave
0
bdoshukAuthor Commented:
I have just tried it against the original source data and it is randomly adding data into different columns. Is that due to the blanks?

Thank you
0
DaveCommented:
working version- finally :)

> This works great. Will it account for extra lines??
Yes

>My original import is over 250000 rows
We may need to go to an array solution for speed then. This range solution may take too long

Cheers

Dave
Sub CreateCSV_Output()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lngRow As Long
    Dim rngArea As Range
    Set ws1 = Sheets(1)
    Set ws2 = Sheets(2)
    Dim rng1 As Range
    Set rng1 = ws1.Columns("A").SpecialCells(xlConstants)
    With Application
        .ScreenUpdating = False
    End With
    ws2.UsedRange.ClearContents
    For Each rngArea In rng1.Areas
    lngRow = lngRow + 1
        rngArea.Offset(0, 1).Copy
        ws2.Cells(lngRow + 1, 1).PasteSpecial Transpose:=True
    Next
    ws1.Range(ws1.[a1], ws1.[a30]).Copy
    ws2.[a1].PasteSpecial Transpose:=True
    With Application
        .ScreenUpdating = True
        .CutCopyMode = False
    End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerCommented:
Can you please mention what happened with my file? Did not work or did not like it?
0
bdoshukAuthor Commented:
i couldn't get it to work for numerous rows, and i preferred the automation of a button and the data onto a different sheet.

It worked for the ranges i supplied but would have been nicer if it worked for named ranges to offer more flexibility.
0
Saqib Husain, SyedEngineerCommented:
Thankyou for the feedback

Saqib
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.