• Populating values in a range based on inputs in another range?

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Populating values in a range based on inputs in another range?

    Author
    Topic
    #2468370
    Hi,
    I have a set of data with Product name in rows and Customer names in columns. In input table (as attached snip), range B6:F20 will contain the order volume. In order to generate order in ERP, I need to build the output table (as attached snip) as shown. In output table range H5:J5 will remain constant as header and the output table will be auto generated based on order volume of products for each customers in Input table.
    I am trying to build any excel formula or VBA to build the output table.
    I am using Office 365.
    Could you please help me to sort the problem??
    Thanks in advance.
    Shazzad
    • This topic was modified 1 month, 4 weeks ago by shazzad.arla.
    Viewing 0 reply threads
    Author
    Replies
    • #2468405

      Try the following macro. Make sure InputTableStart and OutputTableStart are set to the first cell of the heading of each table.

      Code:
      Sub ProduceOutputTable()
      Dim InputTableStart, OutputTableStart As String
      Dim NoCustomers, NoProducts, LoopCustomers, LoopProducts, Volume, OutputRow, NoOutputRows, NoOutputCols As Long
      
      'Initialise - update to reflect your data
      InputTableStart = "A2"
      OutputTableStart = "H2"
      OutputRow = 0
      
      'Clear output table but only if there is already data there
      If Range(OutputTableStart).Offset(1, 0) <> "" Then
      Range(Cells(Range(OutputTableStart).Row + 1, Range(OutputTableStart).Column), _
      Cells(Range(OutputTableStart).End(xlDown).Row, Range(OutputTableStart).Column + 2)).Clear
      End If
      
      'Determine number of customers and products
      NoCustomers = Range(InputTableStart).End(xlToRight).Column - Range(InputTableStart).Column
      NoProducts = Range(InputTableStart).End(xlDown).Row - Range(InputTableStart).Row
      For LoopCustomers = 1 To NoCustomers
      For LoopProducts = 1 To NoProducts
      Volume = Range(InputTableStart).Offset(LoopProducts, LoopCustomers)
      If Volume > 0 Then 'write output
      OutputRow = OutputRow + 1
      Range(OutputTableStart).Offset(OutputRow, 0).Value2 = Range(InputTableStart).Offset(0, LoopCustomers).Value2
      Range(OutputTableStart).Offset(OutputRow, 1).Value2 = Range(InputTableStart).Offset(LoopProducts, 0).Value2
      Range(OutputTableStart).Offset(OutputRow, 2).Value2 = Range(InputTableStart).Offset(LoopProducts, LoopCustomers).Value2
      End If
      Next 'LoopProducts
      Next 'LoopCustomers
      Range(OutputTableStart).Select
      MsgBox "Done."
      End Sub
      
    Viewing 0 reply threads
    Reply To: Populating values in a range based on inputs in another range?

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: