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

Topic

New Reply

[shazzad.arla]

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 9 months, 3 weeks ago by shazzad.arla.

Reply | Quote

Replies

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

Reply | Quote