title: Matrix to column excel vba code
body: <p>I have a matrix of usernames running along the top and application names(wraps) running down the side. We have put an X in each cell where a particular person uses an application but we now need to make a standard table of 2 columns: Username &amp; App Name and then list the users and the associated application used.</p>
  
  <p>Matrix looks like:</p>
  
  <pre>
  Username| jsmith| bspence| tjones 
   Wrap ID|
       abc|           X        X
       def|   X       O
       ghi|   X       X
  </pre>
  
  <p>I need to change to the format:</p>
  
  <pre>
  Username|WrapID | value
  Jsmith | abc     | X
  Jsmith | ghi     | X
  bspence | def    | O
  bspence | ghi    | X
  tjones | abc     | X
  </pre>
  
  <p>I have tried joining every formula I can think of, if(index(match) among others and am at a complete loss. I dont know any VB but it looks like this is the only solution to the problem.</p>
  
  <p>Any assistance is sincerely appreciated.</p>
  
  <p>I made a code like this, but this give me errors.</p>
  
  <pre><code>Sub ConvertMatrix() 
  Dim lngX As Long, vIn, vUser, vOut 
  Dim i As Long, j As Long, rngIn As Range, k As Long 
  Set rngIn = [a1].CurrentRegion 
  vIn = rngIn.Offset(1, 0).Resize(rngIn.Rows.Count - 1).Value 
  vUser = rngIn.Resize(, rngIn.Columns.Count - 1).Offset(, 1).Rows(1).Value 
  lngX = Application.WorksheetFunction.CountIf(rngIn, "X") 
  Redim vOut(1 To lngX, 1 To 3) 
  For i = 1 To UBound(vUser, 2) 
      For j = 1 To UBound(vIn, 1) 
          If vIn(j, i + 1) = "X" Then 
              k = k + 1 
              vOut(k, 1) = vUser(1, i) 
              vOut(k, 2) = vIn(j, 1) 
              vOut(k, 3) = vIn(j, i + 1)
          End If 
      Next 
  Next 
  With Worksheets.Add 
      .Range("A1:B1") = Array("User", "WrapID", "value") 
      .Range("A2").Resize(UBound(vOut, 1), 3).Value = vOut 
  End With 
  </code></pre>
  
  <p>End Sub </p>
  
  <p>Many Thnaks</p>
  
  <p>Regards,</p>