Covert cells with multiple data to rows

[maxnimrod]

We use a SharePoint library for end users to submit requests for changes. Because they can submit multiple lines of changes, this produces an output file like the one below.

I don't mind copying the data and pasting it to another sheet. However that can be quite time consuming when there are dozens of requests. Plus, other people may process these so I need an easier solution.

[TABLE="width: 673"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]Row1[/TD]
[TD]Record #[/TD]
[TD]Date[/TD]
[TD]Requestor[/TD]
[TD]R#[/TD]
[TD]U#[/TD]
[TD]Data1[/TD]
[TD]Data2[/TD]
[TD]Data3[/TD]
[TD]# of Requests[/TD]
[/TR]
[TR]
[TD]Row2[/TD]
[TD]101[/TD]
[TD]4-Jul[/TD]
[TD]John[/TD]
[TD]2001[/TD]
[TD]ABC[/TD]
[TD]Red[/TD]
[TD]Green[/TD]
[TD]Yes[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]2002[/TD]
[TD]DEF[/TD]
[TD]Brown[/TD]
[TD]Orange[/TD]
[TD]No[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]2003[/TD]
[TD]GHI[/TD]
[TD]Purple[/TD]
[TD]Blue[/TD]
[TD]No[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]2004[/TD]
[TD]JKL[/TD]
[TD]Yellow[/TD]
[TD]Pink[/TD]
[TD]Yes[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2005[/TD]
[TD]MNO[/TD]
[TD]Black[/TD]
[TD]White[/TD]
[TD]No[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Row3[/TD]
[TD]104[/TD]
[TD]7-Jul[/TD]
[TD]Mike[/TD]
[TD]3011[/TD]
[TD]PFY[/TD]
[TD]Red[/TD]
[TD]Green[/TD]
[TD]Yes[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]3012[/TD]
[TD]GHK[/TD]
[TD]Brown[/TD]
[TD]Orange[/TD]
[TD]No[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]3013[/TD]
[TD]DCC[/TD]
[TD]Purple[/TD]
[TD]Blue[/TD]
[TD]No[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]3014[/TD]
[TD]MMZ[/TD]
[TD]Yellow[/TD]
[TD]Pink[/TD]
[TD]Yes[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3015[/TD]
[TD]XGF[/TD]
[TD]Black[/TD]
[TD]White[/TD]
[TD]No[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]105[/TD]
[TD]9-Jul[/TD]
[TD]Jack[/TD]
[TD]2009[/TD]
[TD]DCF[/TD]
[TD]Green[/TD]
[TD]Black[/TD]
[TD]No[/TD]
[TD]1[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="5"><col></colgroup>[/TABLE]

For example, On July 4th, John submitted a request that contained 5 changes. His request first request # is 2001 and he needs artifact ABC changed from Red to Green. And yes or no for manager approval.
Column I contains the number of requests Each user submitted, so that may be helpful. You will notice that the request in row 4 only contained 1 request. So those would be ok as they are.

I need an easy way to put each row of data that a user submits into it's own row. I don't care so much if columns A, B and C don't get copied down. It is easy enough to drag them after the fact.

Any advice would be greatly appreciated.
Thank

 

[maxnimrod]

Hey guys))

No worries about the time Frank. Our personal lives are what's important.
I appreciate all the work you both put into this. I can get both of these to run without a problem, but it looks like the issue remains the same. The data in D-G doesn't seem to have any delimiter and that is what is causing this. So what happens with frank_AL's script is that it stops when it gets to cloud D. and Fluff script just duplicates the rows with the same formatting as it started with.

It looks like (and this is from pasting the data to plain text) that in each one of those cells, the data is enclosed in quotes. Ex. D2 would look like this: "39823053990521399060139906053990607399060839928293992835399285039928743992878" All those request ID's are the same length. But in the rest of the columns, that wouldn't be the case.

It almost seems to me there isn't a way to iron this out. The data is submitted through an InfoPath form that has a repeating table. So that is why SharePoint stores the data the way it does. But without any common delimiter, I don't see a way this could work. But you guys know code much better than me. I had a post in the SharePoint forums for a while last year, but they basically dismissed it as it is the way the query builds the data. I may have to rework the process so it creates one row for each line item instead of grouping them together.

Thanks again for all your help. It is greatly appreciated.

 

[Fluff]

If you expand column D so that it is 4 or 5 times wider then needed, do you get all the numbers one below the other, or do you get some of them side by side?

 

[maxnimrod]

No matter how far you expand the columns the data stays stacked one on top of the other in order.

 

[maxnimrod]

Sorry, the text is wrapped by default. If you remove the wrapping, they all run together. I should have noticed that.

 

[Fluff]

OK in the sheet press
Ctrl F
in the Find what box type
Alt 0010 (using the number keypad) & then click Find All.
Does it find anything? if not do the same again, but lookfor
Alt 0013

 

[maxnimrod]

Oh, that's interesting.
I got a hit on 0010 in every cell in every column that has stacked data. D-H. It does not hit on those columns when only one value exists in those cells.

 

[Fluff]

Oh, that's interesting.
I got a hit on 0010 in every cell in every column that has stacked data. D-H. It does not hit on those columns when only one value exists in those cells.

From what you said in post#13 I suspected you'd say something like that.
try this

' maxnimrod (zz2)

    Dim Usdrws As Long
    Dim Rws As Long
    Dim Cnt As Long
    Dim Cols As Long

Application.ScreenUpdating = False

    Usdrws = Range("A" & Rows.Count).End(xlUp).Row
    
    For Rws = Usdrws To 2 Step -1
        Cnt = Range("I" & Rws).Value
        If Cnt > 1 Then
            Rows(Rws + 1).Resize(Cnt - 1).Insert
            Range("A" & Rws & ":C" & Rws).Resize(Cnt).FillDown
            For Cols = 4 To 8
                Cells(Rws, Cols).Resize(Cnt).Value = Application.Transpose(Split(Cells(Rws, Cols).Value, Chr(10)))
            Next Cols
        End If
    Next Rws

End Sub

 

[maxnimrod]

Wow, you nailed it!!

It works perfectly. I ran it against a fresh list that I just pulled.

That turned out to be a prefect test because people left out request numbers and some other data. It left the blanks just where they should be. And it also ignored two entries that were single line items. What else can I say besides THANK YOU!!

I really appreciate what you and everyone else does here. This will save me a lot of time and it will make it easier for other people to cover this process when I am out.

Thanks again ))

 

[Fluff]

Glad to help & thanks for the feedback