Advanced transpose or vba

[fhaid]

Dear All

I want to convert attached plan to vertically, not sure how it’s going to be done.
Your help and advice is much appreciated.

Plan with required output is on the following link.

https://drive.google.com/open?id=0B_...VNhVFJsNTZrcmc

 

[Norie]

What's to be done with the values in the 'middle' of the plan, eg in columns D onwards starting in column 2?

Those values don't seem to appear anywhere on the 'Output' tab.

 

[fhaid]

Those values are appearing in output.
On D5 we have 3 spots. In Output sheet we split those 3 spots from A2 to A5 against the Channel Name, Hour and Date.

Hope you understand now

 

[Rick Rothstein]

Those values are appearing in output.
On D5 we have 3 spots. In Output sheet we split those 3 spots from A2 to A5 against the Channel Name, Hour and Date.

The actual question Norie was asking is what happens to the data in Columns E, F, G, etc.? Does Column E's data go under Column D's data and then Column F's data under Column E's data, and so on until all of your data is in 4 columns on the output sheet?

 

[fhaid]

All the data in Columns E,F,G, etc. will go in column D (Output Sheet) and it will split like I mentioned before

 

[Rick Rothstein]

All the data in Columns E,F,G, etc. will go in column D (Output Sheet) and it will split like I mentioned before

Okay, now, about the numbers in the Output sheet... what is the logic that you used to derive them from what you show on the Plan sheet? I cannot see any rhyme or reason for the values you show on the Outsheet given the values you show on the Plan sheet... what is the logic behind how the data on the Output sheet is constructed?

 

[fhaid]

Logic here is to transform the plan into vertical plan.
If I place 3 spots at 7 on 1st Jan at Channel A. It has to be convert it into vertically where all those 3 spots split in single line against the Channel date, Hour and Duration.

We have two channels here Channel A and Channel B

So how it works, First it read Column A,B,C and D. It extract the channel name, the hour , duration, At column there are number of spots in place so If macro doesnt find any spot on D2, it will move to D3, then D4 and onwards. When it finds 3 spots it will place in the output sheet and move on to D6 and onwards until it finds the other name of the Channel i.e. "Channel B". Macro then move to E2 and do the same process and then F, G,H, etc. colummns. When its done it will move to Channel B and do the same process.

I guess its a bit tricky but I am not sure whether its be done easily.

 

[Rick Rothstein]

Okay, I think the following code will do what you want. Note that it assumes your data sheet is named "Plan" and that the output will go to a sheet named "Output" which the code assumes already exists. Also note that the code will clear the Output sheet of any data before placing the newly calculated data on the sheet... this clearing of the sheet is irreversible and unrecoverable, so if you have any data on the sheet from a previous run, that data will be lost, so make sure such previous data has been saved somewhere before running the code again.

[table="width: 500"]
[tr]
	[td]Sub RearrangeChannelDateData()
  Dim R As Long, C As Long, X As Long, Z As Long, Data As Variant, Result As Variant
  Data = Sheets("Plan").Range("A1").CurrentRegion
  ReDim Result(1 To Application.Sum(Sheets("Plan").Range("A1").CurrentRegion.Offset(1, 3)), 1 To 4)
  For C = 4 To UBound(Data, 2)
    For R = 2 To UBound(Data, 1)
      If Not Data(R, C) Like "*[!0-9]*" Then
        For X = 1 To Data(R, C)
          Z = Z + 1
          Result(Z, 1) = Data(R, 1)
          Result(Z, 2) = Data(1, C)
          Result(Z, 3) = Data(R, 2)
          Result(Z, 4) = Data(R, 3)
        Next
      End If
    Next
  Next
  Sheets("Output").UsedRange.Clear
  Sheets("Output").Range("A2").Resize(UBound(Result), 4) = Result
End Sub[/td]
[/tr]
[/table]

 

[fhaid]

Thanks Nick, Its Incredible !!!