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;

Wo No	Directive	Actual Completion	Reference No	Nt	Ot1	Ot2	Ot3	Ot4	Ns
92034	Full Overhaul KRV1025618 36-SV-1623B 8.75 6x10	12/08/2019	AG10183391	1	0.5	2	3	0.5	1

<tbody>
</tbody>

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;

Wo No	Directive	Actual Completion	Reference No	Rate	Hours
92034	Full Overhaul KRV1025618 36-SV-1623B 8.75 6x10	12/08/2019	AG10183391	Nt	1
92034	Full Overhaul KRV1025618 36-SV-1623B 8.75 6x10	12/08/2019	AG10183391	Ot1	0.5
92034	Full Overhaul KRV1025618 36-SV-1623B 8.75 6x10	12/08/2019	AG10183391	Ot2	2
92034	Full Overhaul KRV1025618 36-SV-1623B 8.75 6x10	12/08/2019	AG10183391	Ot3	3
92034	Full Overhaul KRV1025618 36-SV-1623B 8.75 6x10	12/08/2019	AG10183391	Ot4	0.5
92034	Full Overhaul KRV1025618 36-SV-1623B 8.75 6x10	12/08/2019	AG10183391	Ns	1

<tbody>
</tbody>

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