Consolidating a list into a Condensed version

[maverick93]

Column A5:A32 is a list of product, In a second column N5:N32 is where a quantity may be entered for the corresponding products in column A. Typically only 4 but a max of 9 of the cells in N5:N32 may have quantities in them the rest will be blank. What I would like to do is create a condensed list of only the products that have quantities in place that list in AE21:AE32 and the coresponding quantities in AF21:AF32.

I would like to do this using a ActiveX CommandButton if possible but if it needs to be done as a function then I can make that work.

Thanks for the help

 

[DanteAmor]

I would like to do this using a ActiveX CommandButton

Try this

Private Sub CommandButton1_Click()
  Dim a As Variant, c As Range, i As Long
  
  ReDim a(1 To 32, 1 To 2)
  For Each c In Range("N5:N32")
    If c.Value <> "" Then
      i = i + 1
      a(i, 1) = Range("A" & c.Row)
      a(i, 2) = c
    End If
  Next
  Range("AE5").Resize(UBound(a), 2).Value = a
End Sub

 

[maverick93]

Thank you DanteAmor,

You code worked spot on!
Thank you for the help

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.