Re-arranging data

[depks]

Consider the following data in Excel
AutoCAD VB VC++ .NET
PC01 Yes Yes
PC02 Yes
PC03 Yes Yes

I want to re-arrange the data in the following format.
PC number Software1 Software2 re 3
PC01 AutoCAD VC++
PC02 VB
PC03 AutoCAD .NET

Thank you in advance.[/img]

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim ShNew As Worksheet
    Dim r As Long
    Dim c As Integer
    Dim Col As Long
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A1").CurrentRegion
    Set ShNew = Worksheets.Add
    ShNew.Cells(1, 1).Value = "PC number"
    For c = 2 To Rng.Columns.Count
        ShNew.Cells(1, c).Value = "Software" & c - 1
    Next c
    Col = 2
    For r = 2 To Rng.Rows.Count
        ShNew.Cells(r, 1).Value = Rng.Cells(r, 1).Value
        For c = 2 To Rng.Columns.Count
            If Rng.Cells(r, c).Value = "Yes" Then
                ShNew.Cells(r, Col).Value = Rng.Cells(1, c).Value
                Col = Col + 1
            End If
        Next c
        Col = 2
    Next r
End Sub

 

[depks]

Thanks a lot Mr.Andrew Poulsom. It worked!

Try:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim ShNew As Worksheet
    Dim r As Long
    Dim c As Integer
    Dim Col As Long
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A1").CurrentRegion
    Set ShNew = Worksheets.Add
    ShNew.Cells(1, 1).Value = "PC number"
    For c = 2 To Rng.Columns.Count
        ShNew.Cells(1, c).Value = "Software" & c - 1
    Next c
    Col = 2
    For r = 2 To Rng.Rows.Count
        ShNew.Cells(r, 1).Value = Rng.Cells(r, 1).Value
        For c = 2 To Rng.Columns.Count
            If Rng.Cells(r, c).Value = "Yes" Then
                ShNew.Cells(r, Col).Value = Rng.Cells(1, c).Value
                Col = Col + 1
            End If
        Next c
        Col = 2
    Next r
End Sub