Extrapolating Columns into Duplicate Lines

[gimmick18]

Hi Experts,

I have something I am struggling to get my head around in terms of Macro code, and wondered if I could ask for help. Sample data below;

[TABLE="width: 500"]
<tbody>[TR]
[TD]Wo No[/TD]
[TD]Directive[/TD]
[TD]Actual Completion[/TD]
[TD]Reference No[/TD]
[TD]Nt[/TD]
[TD]Ot1[/TD]
[TD]Ot2[/TD]
[TD]Ot3[/TD]
[TD]Ot4[/TD]
[TD]Ns[/TD]
[/TR]
[TR]
[TD]92034[/TD]
[TD]Full Overhaul KRV1025618 36-SV-1623B 8.75 6x10[/TD]
[TD]12/08/2019[/TD]
[TD]AG10183391[/TD]
[TD]1[/TD]
[TD]0.5[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]0.5[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


This is an export from a system that highlights the number of hours worked by various staff Rates (Nt, Ot1, Ot2 etc...).

The output from this needs to show a separate line for each Rate, therefore replicating the contents to produce this;

[TABLE="width: 500"]
<tbody>[TR]
[TD]Wo No[/TD]
[TD]Directive[/TD]
[TD]Actual Completion[/TD]
[TD]Reference No[/TD]
[TD]Rate[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]92034[/TD]
[TD]Full Overhaul KRV1025618 36-SV-1623B 8.75 6x10[/TD]
[TD]12/08/2019[/TD]
[TD]AG10183391[/TD]
[TD]Nt[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]92034[/TD]
[TD]Full Overhaul KRV1025618 36-SV-1623B 8.75 6x10[/TD]
[TD]12/08/2019[/TD]
[TD]AG10183391[/TD]
[TD]Ot1[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]92034[/TD]
[TD]Full Overhaul KRV1025618 36-SV-1623B 8.75 6x10[/TD]
[TD]12/08/2019[/TD]
[TD]AG10183391[/TD]
[TD]Ot2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]92034[/TD]
[TD]Full Overhaul KRV1025618 36-SV-1623B 8.75 6x10[/TD]
[TD]12/08/2019[/TD]
[TD]AG10183391[/TD]
[TD]Ot3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]92034[/TD]
[TD]Full Overhaul KRV1025618 36-SV-1623B 8.75 6x10[/TD]
[TD]12/08/2019[/TD]
[TD]AG10183391[/TD]
[TD]Ot4[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]92034[/TD]
[TD]Full Overhaul KRV1025618 36-SV-1623B 8.75 6x10[/TD]
[TD]12/08/2019[/TD]
[TD]AG10183391[/TD]
[TD]Ns[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Is this even achievable?

Basically for each row in a set of Data I need copies of that in another Worksheet "per rate". So if the data set for example had 10 Rows with 6 different rates....it would transpose into 60 rows of one rate per row.

Messy and I've argued the case for changing the output of the report, but it's what the customer wants.

Anyone seen anything like this?

Thanks in advance,
Kevin

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub gimmick18()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long, i As Long
   
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * (UBound(Ary, 2) - 4), 1 To 6)
   For r = 2 To UBound(Ary)
      For c = 5 To UBound(Ary, 2)
         nr = nr + 1
         For i = 1 To 4
            Nary(nr, i) = Ary(r, i)
         Next i
         Nary(nr, 5) = Ary(1, c)
         Nary(nr, 6) = Ary(r, c)
      Next c
   Next r
   Sheets("Sheet2").Range("A2").Resize(nr, 6).Value = Nary
End Sub

 

[MickG]

Try this for results on Sheet2.

[COLOR="Navy"]Sub[/COLOR] MG14Aug13
[COLOR="Navy"]Dim[/COLOR] Ray, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = ActiveSheet.Range("A1").CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * 6, 1 To 6)
nray(1, 1) = "Wo No": nray(1, 2) = "Directive": nray(1, 3) = "Actual Completion"
nray(1, 4) = "Reference No": nray(1, 5) = "Rate": nray(1, 6) = "Hours"

c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
   [COLOR="Navy"]For[/COLOR] rw = 5 To 10
     c = c + 1
      [COLOR="Navy"]For[/COLOR] Ac = 1 To 4: nray(c, Ac) = Ray(n, Ac): [COLOR="Navy"]Next[/COLOR] Ac
        nray(c, 5) = Ray(1, rw)
          nray(c, 6) = Ray(n, rw)
   [COLOR="Navy"]Next[/COLOR] rw
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 6)
    .Value = nray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[gimmick18]

Wow, an absolutely fantastic solution. Efficient, fast and not processor heavy. Thank you so much Fluff, very much appreciated. Got everything going now including reformatting of the output data.

 

[Fluff]

You're welcome & thanks for the feedback