Excel items from SharePoint.

I have SharePoint 2010 List items that and exported to Excel 2013.
Some of the items are from a Multi item lookup in SharePoint.

 SharePoint-Excel-example-A.jpg
I'm deleting one of the rows and creating a report with the remaining row.
In columns H, I, etc. contain  "desired data"  with  " ;#number;# "  between each item, then  "desired data" again.
I have a macro to clean up the data.
I then copy and transpose the data on a new sheet.
SharePoint-Excel-example-B.jpgI then run text to columns based on the semicolon.
I then transpose the data again to convert it back to rows in the original location.
SharePoint-Excel-example-c.jpg
this makes the report easier to read and understand.

Is there an easier way to move items that follow the semicolon down to the next row in each column.
There has to be an easier way.

Any help would be great.

Thank you
idealaeroAsked:
Who is Participating?
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.

Rodney EndrigaData AnalystCommented:
idealaero, you can use the SPLIT function to extract certain text between the ";#" you have in a cell. You can incorporate this into your macro:

Sub ee_Place_DesiredData_inRows()
    Dim DesiredData() As String
    DesiredData() = Split(Cells(1, 1).Value, ";#")
    For i = LBound(DesiredData) To UBound(DesiredData)
        Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = DesiredData(i)
    Next i
End Sub

Open in new window


This assumes your data is in Cell A1, adjust accordingly.
I'm not sure what your current macro looks like, but maybe you can adjust it using this code.
0
idealaeroAuthor Commented:
Thank you for your response,

Your split function works great for 1 column.
I have 14 Columns and I tweaked it to run against the 2nd and 3rd columns.

It shifts data from the 2nd and 3rd columns down below the 1st column in the correct column.

Data cleanup marco

Sub cleaner()

    Cells.Replace What:=";#*;", Replacement:="#", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=";#*", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub


Data After Macro:
excel-issue-aftercleanup.jpg
I then run your script I modified.

Sub moveColumn8()
    Dim DesiredData() As String

        DesiredData() = Split(Cells(2, 8).Value, "##")
    For i = LBound(DesiredData) To UBound(DesiredData)
        Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Value = DesiredData(i)
    Next i
End Sub
Sub moveColumn9()
    Dim DesiredData() As String

        DesiredData() = Split(Cells(2, 9).Value, "##")
    For i = LBound(DesiredData) To UBound(DesiredData)
        Cells(Rows.Count, 9).End(xlUp).Offset(1, 0).Value = DesiredData(i)
    Next i
End Sub
Sub moveColumn10()
    Dim DesiredData() As String

        DesiredData() = Split(Cells(2, 10).Value, "##")
    For i = LBound(DesiredData) To UBound(DesiredData)
        Cells(Rows.Count, 10).End(xlUp).Offset(1, 0).Value = DesiredData(i)
    Next i
End Sub


Results after.

excel-issue-aftercleanup.jpg
How would I stop the down stepping?


Thank you

John
0
Rodney EndrigaData AnalystCommented:
Hi John, what do you mean by "down stepping"?

The macro will basically perform a pasting of the cell contents based on the "##" delimiter.

Does the macro continue loop or does it not complete correctly?
Please advise.

You can also condense your above code to:
Sub moveMultiColumnsData()
     Dim DesiredData() As String
for g=8 to 10
         DesiredData() = Split(Cells(2, g).Value, "##")
     For i = LBound(DesiredData) To UBound(DesiredData)
         Cells(Rows.Count, g).End(xlUp).Offset(1, 0).Value = DesiredData(i)
     Next i
next g
 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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

idealaeroAuthor Commented:
The macro does complete.


an example of down stepping.

current results.
Column 1 output          Column 2 output        Column3 output


1  c1 item1
2  c1  item2
3  c1  item3
4                                       c2 item1
5                                       c2 item2
6                                       c2 item3
7                                                                                 c3 item1
8                                                                                 c3 item2
9                                                                                 c3 item2


Desired results:

Column 1 output          Column 2 output        Column3 output


1  c1 item1                     c2 item1                         c3 item1
2  c1  item2                    c2 item2                          c3 item2
3  c1  item3                    c2 item3                         c3 item2


Does this help?

John
0
Rodney EndrigaData AnalystCommented:
Thank you for the clarification, John.

Please see my adjusted code above, it should not perform "down stepping". It should just give you the desired results output.
0
idealaeroAuthor Commented:
Rodney,

The down stepping was caused by the data format on the worksheet.
I added a macro step that copied the worksheet to a new one and pasted it as values and everything worked perfectly after that.

Thank you

John
0
Rodney EndrigaData AnalystCommented:
Perfect, thank you for the points! Glad you were able to find a solution. Happy coding!
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.