Need Help converting columns to YML format

[trevor2524]

Hello All,

Looking for some help on how to achieve the following. I have two sheets. In Sheet1 i have three columns (Variable, Value, Server) with various forms of data. I'm looking to transfer that data into a specific format on sheet2. It would be setup in YML format with the variable name as the main category. Then each server would be listed out with the value listed after it. Does anyone have any ideas on how i can achieve this. Your help is appreciated. 30500

[YasserKhalil]

Try this code


Sub Test()
Dim txt As String, f As Integer, iRow As Long, iCol As Long
With Sheet1
For iRow = 1 To WorksheetFunction.CountA(.Range("A:A"))
For iCol = 1 To WorksheetFunction.CountA(.Range("1:1"))
txt = txt & .Cells(1, iCol) & ": " & .Cells(iRow, iCol) & vbCrLf
Next iCol
Next iRow
End With
f = FreeFile
Open ThisWorkbook.Path & "\Output.yml" For Output As #f
Print #f, txt
Close #f
End Sub

[trevor2524]

Thanks for the reply. It's working just need the outcome changed a little. The Headers of Variable Name, Value & Server should not show up on the output. And after the variable name there should be ":" and after the server name before the value there should be a ":".. Sheet 2 give an example on the ouput look.

[YasserKhalil]

Try this version



Sub Test()
Dim txt As String, prevVar As String, f As Integer, iRow As Long
With Sheet1
prevVar = .Cells(2, 1)
txt = .Cells(2, 1) & ":" & vbCrLf & " " & .Cells(2, 3) & ": " & .Cells(2, 2) & vbCrLf
For iRow = 3 To WorksheetFunction.CountA(.Range("A:A"))
If prevVar <> .Cells(iRow, 1) Then
prevVar = .Cells(iRow, 1)
txt = txt & vbCrLf & prevVar & ":" & vbCrLf & " " & .Cells(iRow, 3) & ": " & .Cells(iRow, 2) & vbCrLf
Else
txt = txt & Space(2) & .Cells(iRow, 3) & ": " & .Cells(iRow, 2) & vbCrLf
End If
Next iRow
End With
f = FreeFile
Open ThisWorkbook.Path & "\Output.yml" For Output As #f
Print #f, txt
Close #f
End Sub

[arnelgp]

another example.