Macro: Transpose data from column into separate rows (automatically insert new rows)

[whataremacros]

Hi, I'm new to this forum and bad at programming macros, although I find them extremely useful.

I would like to have the sports column to be transposed as rows. I have provided a sample file at https://onedrive.live.com/?id=131627F5FABFC19!139&cid=0131627F5FABFC19

I can do the job manually by
1. Adjust the other columns (C D E... etc) to about 20 columns away.
2. Use data to columns (delimited by ";").
3. Insert additional rows in between the existing rows.
4. Copy "sports" data separated by columns
5. Special Paste> Transpose
6. Copy the rest of the information in the added rows
7. Move on to the next item and repeat 1-6

The thing is my file is composed of a lot of entries and repeating the process would take time.
Can anyone help me with this matter?
It will be surely appreciated. Thank you.

I found a partial solution on this thread http://www.mrexcel.com/forum/excel-...umns-rows-automatically-inserts-new-rows.html
I believe my issue should not be a big deviation from the solution at the link above, but I just don't know how to modify it to fit my needs.

I'm using Excel 2007 on Windows 7. I can also get access to Excel 2016 on Win 7, if needed.

P.S. Links to a starting guide with macros for a total newbie would be welcome

 

[MickG]

Try this for Results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG15May19
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = ActiveSheet.Range("A1").CurrentRegion
ReDim nray(1 To UBound(Ray, 2), 1 To 1)

[COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray, 1)
    Sp = Split(Ray(n, 2), ";")
    [COLOR="Navy"]For[/COLOR] s = 0 To UBound(Sp)
        c = c + 1
        ReDim Preserve nray(1 To UBound(Ray, 2), 1 To c)
        [COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray, 2)
            [COLOR="Navy"]If[/COLOR] Ac = 2 [COLOR="Navy"]Then[/COLOR]
                nray(Ac, c) = Sp(s)
            [COLOR="Navy"]Else[/COLOR]
                nray(Ac, c) = Ray(n, Ac)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Ac
     [COLOR="Navy"]Next[/COLOR] s
[COLOR="Navy"]Next[/COLOR] n
Sheets("sheet2").Range("a1").Resize(c, UBound(Ray, 2)) = Application.Transpose(nray)
MsgBox "Run"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[whataremacros]

Thanks Mick

I really appreciate your time and effort. It worked perfectly once I renamed the sheet to "sheet2".

I applied the same code on another file (same format, different data, non-zero values), but this time I got run-time error '13: Type mismatch
Any ideas what might be going wrong?

Thanks again for your help

 

[MickG]

I think you will need to show an example of your new data, also the specify number of rows and columns in new data

 

[whataremacros]

I think you will need to show an example of your new data, also the specify number of rows and columns in new data

Thanks for replying. I have placed the new file at https://onedrive.live.com/redir?resid=131627F5FABFC19!215

I have made two sheets, RawData and ExpectedOutput, the latter containing expected output for first 5 lines of RawData.

I will have to apply the macro on several files, each with a different set of data. The columns will remain the same as in the attached sheet i.e. 11 (up to K), but the number of rows can vary from 10 to 1000.

The macro supplied by you gives the Type Mismatch error, once I run on this file. I tried with replacing ";" with "," and ", " as the data is separated by a comma and space.
The macro at this link works on attached file without error, once I change delimiter from ";" to "," but obviously it doesn't do the complete job.

For full disclosure, the attached sheet contains merged cells and conditional formatting. Both can be removed if there's no workaround.

Again, I appreciate your help a lot.

 

[MickG]

The code will have problems with "Merged Cells", If you remove them does the code then work ????

 

[whataremacros]

The code will have problems with "Merged Cells", If you remove them does the code then work ????

No, it doesn't, I tried that too. Same Type Mismatch error.

 

[whataremacros]

No, it doesn't, I tried that too. Same Type Mismatch error.

Sorry, my bad. It works once "Merged Cells" are removed.

Thanks a lot.

I tried removing merged cells yesterday but it didn't work then, it works now (maybe I made a mistake yesterday).

Cheers

 

[MickG]

You're welcome