Excel/Macros/codes

Ralive

New Member
Joined
Jul 10, 2014
Messages
9
Hi, I am new to this Forum, and don't have extensive knowledge of Excel.

I exported a list from SharePoint to Excel and they got exported as shown on example 1. Is there any codes/macros to convert them to display them as example 2. The lists have hundreds of names, and I have to export the list on monthly basis, so doing it manually would take a lot of time. Any kind of help is highly appreciated. Please let me know if you need more info.


Example 1

<tbody>
</tbody>

Title
Name
Address
Manager
Tom, Harry, Ram
NY, VA, NJ

<tbody>
</tbody>





Example 2
Title
Name
Address
Manager
Tom
NY
Manager
Harry
VA
Manager
Ram
NJ

<tbody>
</tbody>


Thanks,
Ralive
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
WELCOME TO THE FORUM

Is the whole data on one line? or you have multiple lines

and are we to assume that all of them have same title (as in Manager?

I see you have edited your post :)
 
Last edited:
Upvote 0
This should work, PLEASE TRY ON A COPY OF THE DATA and change the sheet names accordingly, I assumed the original data is in sheet2 and the output is in sheet3

Code:
Sub rearrangedata()
    Dim wks As Worksheet, wks2 As Worksheet, wf As WorksheetFunction
    
    Set wks = Worksheets("Sheet2")
    Set wks2 = Worksheets("Sheet3")
    Set wf = Application.WorksheetFunction
    
    For I = wks.Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        Name = Split(wks.Range("B" & I).Value, ",")
        addr = Split(wks.Range("C" & I).Value, ",")
        For k = 1 To UBound(Split(wks.Range("B" & I).Value, ",")) + 1
            lrow = wks2.Range("A" & Rows.Count).End(xlUp).Row
            With wks2
                .Cells(lrow + 1, 1) = wks.Cells(I, 1)
                .Cells(lrow + 1, 2) = Name(k - 1)
                .Cells(lrow + 1, 3) = addr(k - 1)
            End With
        Next k
    Next I
                
    
End Sub
 
Upvote 0
Thank you so much Momentman for your reply. Sorry, if I was not clear on my post. I have given another examples below which may add more information/sense to my post.
Where should I add the codes you provided on your reply? I am novice to excel and don't have much knowledge on it.
Title
Name
Address
Manager
Tom, Ram, Harry
NJ, NY, VA
Analyst
Bob, Tina
NY, CA
Intern
Vicky, Sam
CO, NJ

<tbody>
</tbody>

Title
Name
Address
Manager
Tom
NJ
Manager
Ram
NY
Manager
Harry
VA
Analyst
Bob
NY
Analyst
Tina
CA
Intern
Vicky
NJ
Intern
Sam
CO

<tbody>
</tbody>

Thanks,
Ralive
 
Upvote 0
Thats not a problem

1. Open the visual editor by pressing ALT+F11
2.On the left hand side, right click and select Insert>module
3.copy and paste the code i posted [ in the plain window to the right]
4. Press F5 to run the code
Note that its advisable to create a copy of your workbook before running the macro as the effects are most times irreversible
 
Last edited:
Upvote 0
Thank you so much Momentman for your response. I was out of the state for a while, so didnt get a chance to test the codes. I tried it the way you said and it worked. Thank you so much. But the data replicated 9 times. I had 3 items on (Name column), but now shows 27 items i,e. each name is repeated 9 times. Am I doing anything wrong here? Sorry for my lack of expertise.Thanks,Ralive
 
Last edited:
Upvote 0
Maybe you are :) That's a joke anyways

See what the macro gave me

Initial data

Excel 2010
ABC
1TitleNameAddress
2ManagerTom, Ram, HarryNJ, NY, VA
3SeniorsJake,Mary,KashmirMO,SC,NC
4AnalystBob, TinaNY, CA
5InternVicky, SamCO, NJ
Sheet2


Output


Excel 2010
ABC
2InternVickyCO
3InternSamNJ
4AnalystBobNY
5AnalystTinaCA
6SeniorsJakeMO
7SeniorsMarySC
8SeniorsKashmirNC
9ManagerTomNJ
10ManagerRamNY
11ManagerHarryVA
Sheet3
 
Upvote 0
I realised that the results are in the reverse, so I change the macro from a FOR loop to a DO loop

Code:
Sub rearrangedata2()
    Dim wks As Worksheet, wks2 As Worksheet, wf As WorksheetFunction
    
    Set wks = Worksheets("Sheet2")
    Set wks2 = Worksheets("Sheet3")
    Set wf = Application.WorksheetFunction
    
    I = 1
    Do
        Name = Split(wks.Range("B" & I).Value, ",")
        addr = Split(wks.Range("C" & I).Value, ",")
        For k = 1 To UBound(Split(wks.Range("B" & I).Value, ",")) + 1
            lrow = wks2.Range("A" & Rows.Count).End(xlUp).Row
            With wks2
                .Cells(lrow + 1, 1) = wks.Cells(I, 1)
                .Cells(lrow + 1, 2) = Trim(Name(k - 1))
                .Cells(lrow + 1, 3) = Trim(addr(k - 1))
            End With
        Next k
    I = I + 1
    Loop While I <= wks.Range("A" & Rows.Count).End(xlUp).Row
    
End Sub

here's the result


Excel 2010
ABC
2TitleNameAddress
3ManagerTomNJ
4ManagerRamNY
5ManagerHarryVA
6SeniorsJakeMO
7SeniorsMarySC
8SeniorsKashmirNC
9AnalystBobNY
10AnalystTinaCA
11InternVickyCO
12InternSamNJ
Sheet3
 
Upvote 0
Here is another macro you can try which should be super fast (it should handle your data, no matter how large, about as fast as is possible)...

Code:
Sub RearrangeData()
  Dim R As Long, X As Long, LastRow As Long, NewRowCount As Long, Index As Long
  Dim DataIn As Variant, DataOut As Variant, DataB() As String, DataC() As String
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  DataIn = Range("A1:C" & LastRow)
  NewRowCount = 2 + UBound(Split(Join(WorksheetFunction.Transpose(Range("B2:B" & LastRow).Value), ","), ","))
  ReDim DataOut(1 To NewRowCount, 1 To 3)
  For R = 1 To LastRow
    DataB = Split(DataIn(R, 2), ",")
    DataC = Split(DataIn(R, 3), ",")
    For X = 0 To UBound(DataB)
      Index = Index + 1
      DataOut(Index, 1) = DataIn(R, 1)
      DataOut(Index, 2) = Trim(DataB(X))
      DataOut(Index, 3) = Trim(DataC(X))
    Next
  Next
  Columns("[COLOR=#FF0000][B]E[/B][/COLOR]").Resize(, 3).Clear
  Range("[COLOR=#FF0000][B]E[/B][/COLOR]1:[COLOR=#FF0000][B]E[/B][/COLOR]" & U[COLOR=#FF0000][/COLOR]Bound(DataOut)).Resize(, 3) = DataOut
End Sub
Output starts in Column E (change the red highlighted column designations to the starting output column of your choice).
 
Last edited:
Upvote 0
Hi Momentman,

You are right, I was doing something wrong. Now the code works perfectly. :) Thank you so much. You are a life saver.

If I want to add new column say 'D" and "E" and display them as above, how should I add them to the codes?

Thanks,
Rukesh
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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