Convert text to column and transpose

[jitendra]

Hi friend,

I have some data like below and I want to change it like the second table by using macro if anyone know please help me and thanks in advance.

Existing data
Country Destination C C Cde Price($) statu
ALBANIA OLO 355 4249,4250,4251,4252 3.5 new
ALBANIA TIRANA 126 422,423,424 6.8 old

Required data
Country Destination Country Code City Code Price($) statu
ALBANIA OLO 355 4249 3.5 new
ALBANIA OLO 355 4250 3.5 new
ALBANIA OLO 355 4251 3.5 new
ALBANIA OLO 355 4252 3.5 new
ALBANIA TIRANA 126 422 6.8 old
ALBANIA TIRANA 126 423 6.8 old
ALBANIA TIRANA 126 424 6.8 old


My required format is
Column 1:-Country
Column 2:- Destination
Column 3:-Country Code
Column 4:-City Code
Column 5rice($)
Column 6:-Status

 

[Venkatp]

Sorry - Its somehow erroring out at .Rows(r + 1).Resize(mxr).Insert
(although it runs fine in the initial runs)

Especially when I run on more than two rows or data. Does it have any memory limitation?

I copied the above set to 20 rows and get the error -

Run-time error '1004':


Application-define or object-define error

 

[Venkatp]

hiker95, actually this code is able to process 100 records with no issues. Except that its not transposing columns - M,N.

 

[hiker95]

Sorry - Its somehow erroring out at .Rows(r + 1).Resize(mxr).Insert
(although it runs fine in the initial runs)

Especially when I run on more than two rows or data. Does it have any memory limitation?

I copied the above set to 20 rows and get the error -

Run-time error '1004':


Application-define or object-define error

Venkatp,

You can see by my last reply screenshots that the new macro does work correctly.

If my latest screenshots are not correct, then, we will need to see your actual raw data workbook.


You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com


If you are not able to provide the above, then, someone else on MrExcel will have to help you.

 

[Venkatp]

Venkatp,

You can see by my last reply screenshots that the new macro does work correctly.

If my latest screenshots are not correct, then, we will need to see your actual raw data workbook.


You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com


If you are not able to provide the above, then, someone else on MrExcel will have to help you.

hiker95,


Thank you for offering this help and your determination.


The code does work but it was kind of inconsistent. If I copy the same data to another sheet and run it again and again - it fails.
I uploaded the file with raw data to Dropbox.
(This only has 10 rows of data out of 100 rows)


(I also want to say that the previous code(the fist version) is working except that I am not able to extend to additional columns I added).


https://www.dropbox.com/s/9i13gt29oqgsv4g/To upload1.xlsx?dl=0


Thank you very much again!
Venkatp

 

[hiker95]

Venkatp,

You keep changing what the raw data looks like?????

We have seen two versions of what your raw data looks like, and, I have created a different macro to solve each request.

And, now we have a third version of your raw data going out to column Q?????

Maybe someone else on MrExcel can assist you.

 

[Venkatp]

hiker95,

Sorry for the confusion, I thought Comments column is causing the trouble and so deleted the Comments column. I found that the issue is due to the records with "one" member wherein mxr value is becoming 0 and so throwing an error.

I added If condition i.e, to process the inserts only when the mxr >0 and it works fine.

Thank you so much for the nice code
Venkatp

 

[hiker95]

hiker95,

Sorry for the confusion, I thought Comments column is causing the trouble and so deleted the Comments column. I found that the issue is due to the records with "one" member wherein mxr value is becoming 0 and so throwing an error.

I added If condition i.e, to process the inserts only when the mxr >0 and it works fine.

Thank you so much for the nice code
Venkatp

Venkatp,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.


Can you post your adjusted macro code?


When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]

 

[Venkatp]

Venkatp,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.


Can you post your adjusted macro code?


When posting VBA code, please use Code Tags - like this:

'Paste your code here.

Sub ReorganizeData_V2()
' hiker95, 06/12/2017, ME575713
Dim r As Long, lr As Long, lc As Long, c As Long, s, i As Long, n As Long, mxr As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  For r = lr To 2 Step -1
    mxr = 0
    For c = 4 To lc Step 1
      If InStr(.Cells(r, c), ", ") > 0 Then
        s = Split(Trim(.Cells(r, c)), ", ")
        If UBound(s) > mxr Then
          mxr = UBound(s)
        End If
      End If
    Next c
    If mxr > 0 Then
    .Rows(r + 1).Resize(mxr).Insert
        For c = 4 To lc Step 1
                      If InStr(.Cells(r, c), ", ") > 0 Then
        s = Split(Trim(.Cells(r, c)), ", ")
        If UBound(s) < mxr Then
          .Cells(r, c).Resize(UBound(s) + 1) = Application.Transpose(s)
        Else
          .Cells(r, c).Resize(mxr + 1) = Application.Transpose(s)
        End If
      End If
    Next c
    End If
  Next r
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

 

[Venkatp]

Venkatp,


And, come back anytime.

For sure hiker95 - it was great meeting you all here.

It's stunning how much Excel can do and how much you can do as well ... Thank you all.

 

[hiker95]

For sure hiker95 - it was great meeting you all here.

Venkatp,

Thanks for the feedback.

You are very welcome. Glad I could help.

Back at you.

And, come back anytime.

It's stunning how much Excel can do and how much you can do as well

See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 05/21/2017

See Reply #2 at the next link:

https://www.mrexcel.com/forum/excel-questions/1008151-preparing-job-interview-excel-test.html