• 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?

    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.
    • This topic was modified 1 month, 4 weeks ago by shazzad.arla.
    Viewing 0 reply threads
    • #2468405

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

      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
      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: