Excel/Macros/codes

[Ralive]

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

 

[Ralive]

Hi Rick,

Thank you for the reply. I tried the codes, but it displays the result on same tab. Also how would I update the codes if I have 3 more columns say "D', "E", and "F"?

Thanks,
Rukesh

 

[Rick Rothstein]

Thank you for the reply. I tried the codes, but it displays the result on same tab. Also how would I update the codes if I have 3 more columns say "D', "E", and "F"?

What tab would you like it to go to then?

Do these new columns have comma delimited lists in them also?

 

[Ralive]

Yes, these new columns have comma delimited items in them too.

My lists are in "Sheet2" and I would like to display the result in "Sheet3".

 

[Rick Rothstein]

Yes, these new columns have comma delimited items in them too.

My lists are in "Sheet2" and I would like to display the result in "Sheet3".

Give this code a try...

Sub RearrangeData()
  Dim R As Long, X As Long, Z As Long, LastRow As Long, NewRowCount As Long, Index As Long
  Dim DataIn As Variant, DataOut As Variant, Data(2 To 6) As Variant
  LastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
  DataIn = Sheets("Sheet2").Range("A1:F" & LastRow)
  NewRowCount = 2 + UBound(Split(Join(WorksheetFunction.Transpose(Sheets("Sheet2").Range("B2:B" & LastRow).Value), ","), ","))
  ReDim DataOut(1 To NewRowCount, 1 To 6)
  For R = 1 To LastRow
    For Z = 2 To 6
      Data(Z) = Split(DataIn(R, Z), ",")
    Next
    For X = 0 To UBound(Data(2))
      Index = Index + 1
      DataOut(Index, 1) = DataIn(R, 1)
      For Z = 2 To 6
        DataOut(Index, Z) = Trim(Data(Z)(X))
      Next
    Next
  Next
  Sheets("Sheet3").Columns("A").Resize(, 6).Clear
  Sheets("Sheet3").Range("A1:A" & UBound(DataOut)).Resize(, 6) = DataOut
End Sub

 

[Ralive]

I got the message
Run-time error '9'
Subscript out of range

 

[Rick Rothstein]

I got the message
Run-time error '9'
Subscript out of range

I tested the code before I posted it and it worked, so I'm not entirely sure what is different about your actual data. If you send a copy of your workbook to me, I should be able to track down what is different and modify my code to account for that difference. My email address is....

rick DOT news AT verizon DOT net

 

[Rick Rothstein]

Okay, I got the file that you sent me, but there is a problem... your actual data looks nothing like what you posted earlier... and the data in Column B is inconsistently delimited. On top of that, when I asked you (in Message #12) if the "new columns" (D:F) contained comma delimited data, you said (in Message #13) yes... but each cell in those "new columns" contains single values with not one of them containing a comma! For those following this thread, here is a sample of what I see in the file the OP sent me...

	A	B	C	D	E	F	G
1	Team Name	2. Team Members	# of Team Members	Nominated by	Quarter	Delivered on time?
2	Studio	John Jones, Mary Jones, Bill Johnson, Phil Donaldson	8	Rahul	4	yes
3	Next Generation	Conners, Kevin James, Jesse (US - Glen Mills) Jacobs, Sally (US - Chicago) Malcolms, Margie (US - Boston) Gottleib, Joshua (US - Boston) Arnold, John (US - Hyderabad) Doe, Jane (US - Glen Mills)	18	Ron	4	yes
4

<tbody>
</tbody>

Note for Column B that some cells contain comma delimited data while others contain Line Feed delimited data that contain commas within each line.

To the OP... you cannot expect to get usable responses if you show us data that looks nothing like your actual data. Can you show us what the above should look like after processing?

 

[Ralive]

Hi Rick,

The list that I posted on this thread earlier were sample lists. I thought if I could get codes for those sample lists I could modify the codes and use for actual list ( I didnt know the codes would be so difficult, sorry for lack of expertise). Thats the reason my previous lists were different than what I sent you. I said "yes" (in message #13) because I was talking about my test(sample) list not this one. I apologize for the confusion.

Here is how the list should look like after processing

1.Team Name</SPAN>​	2.Team Members</SPAN>​	# of Team Members</SPAN>​	Nominated by</SPAN>​	Quarter</SPAN>​	Delivered on time?</SPAN>​
Studio</SPAN>​	John Jones</SPAN>​	8</SPAN>​	Rahul</SPAN>​	4</SPAN>​	yes</SPAN>​
Studio</SPAN>​	Mary Jones</SPAN>​	8</SPAN>​	Rahul</SPAN>​	4</SPAN>​	yes</SPAN>​
Studio</SPAN>​	Bill Johnson</SPAN>​	8</SPAN>​	Rahul</SPAN>​	4</SPAN>​	yes</SPAN>​
Studio</SPAN>​	Phil Donaldson</SPAN>​	8</SPAN>​	Rahul</SPAN>​	4</SPAN>​	yes</SPAN>​
Next Generation</SPAN>​	Conners</SPAN>​	18</SPAN>​	Ron</SPAN>​	4</SPAN>​	yes</SPAN>​
Next Generation</SPAN>​	Kevin James</SPAN>​	18</SPAN>​	Ron</SPAN>​	4</SPAN>​	yes</SPAN>​
Next Generation</SPAN>​	Jesse (US - Glen Mills) Jacobs</SPAN>​	18</SPAN>​	Ron</SPAN>​	4</SPAN>​	yes</SPAN>​
Next Generation</SPAN>​	Sally (US - Chicago) Malcolms</SPAN>​	18</SPAN>​	Ron</SPAN>​	4</SPAN>​	yes</SPAN>​
Next Generation</SPAN>​	Margie (US - Boston) Gottleib</SPAN>​	18</SPAN>​	Ron</SPAN>​	4</SPAN>​	yes</SPAN>​
Next Generation</SPAN>​	Joshua (US - Boston) Arnold</SPAN>​	18</SPAN>​	Ron</SPAN>​	4</SPAN>​	yes</SPAN>​
Next Generation</SPAN>​	John (US - Hyderabad) Doe</SPAN>​	18</SPAN>​	Ron</SPAN>​	4</SPAN>​	yes</SPAN>​
Next Generation</SPAN>​	Jane (US - Glen Mills)</SPAN>​	18</SPAN>​	Ron</SPAN>​	4</SPAN>​	yes</SPAN>​

<TBODY> </TBODY>

 

[Rick Rothstein]

Hi Rick,

The list that I posted on this thread earlier were sample lists. I thought if I could get codes for those sample lists I could modify the codes and use for actual list ( I didnt know the codes would be so difficult, sorry for lack of expertise). Thats the reason my previous lists were different than what I sent you. I said "yes" (in message #13) because I was talking about my test(sample) list not this one. I apologize for the confusion.

For future reference... do not simply your data or how it is laid out when asking question since all that will get you (as you have seen) is a great answer to a question you do not really have and which you do not really care about.

For clarification (as it affects the method that will be used in the code).... you will ever only have one column that has a comma delimited list in it (all the rest will have single values), correct?

 

[Ralive]

Yes, thats correct.