How to populate values in a range based on inputs in another range?-CodePudding

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 and the 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.

CodePudding user response：

Unpivot Data (No Duplicates)

This is a simplified version since there are no duplicates in the first column or the first row.
It will still work if you add more columns.

Option Explicit

Sub UnpivotDataCRV()
    
    ' 1.) Define constants.
    
    Const wsName As String = "Sheet1"
    Const sFirstCellAddress As String = "A5"
    
    Const dcCount As Long = 3 ' fixed!
    
    Const cGap As Long = 1 ' empty columns in-between (>0)
    
    ' 2.) Reference the worksheet ('ws').
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    
    ' 3.) Reference the source range ('srg')
    '     (and the destination first row range ('dfrrg')).
    
    Dim srCount As Long
    Dim scCount As Long
    Dim srg As Range
    Dim dfrrg As Range
    
    With ws.Range(sFirstCellAddress)
        ' Assuming the range has at least two rows and its first column
        ' has no empty cells.
        Dim slRow As Long: slRow = .End(xlDown).Row
        ' Assuming the range has at least two columns and its first (header) row
        ' has no empty cells.
        Dim slCol As Long: slCol = .End(xlToRight).Column
        srCount = slRow - .Row   1
        scCount = slCol - .Column   1
        Set srg = .Resize(srCount, scCount)
        Set dfrrg = .Offset(1, scCount   cGap).Resize(, dcCount)
    End With
    
    ' 4.) Write the values from the source range to the source array ('sData'),
    '     a 2D one-based array.
    
    Dim sData() As Variant: sData = srg.Value
    
    ' 5.) Count the number of destination rows ('drCount').
    
    Dim drCount As Long
    With srg.Resize(srCount - 1, scCount - 1).Offset(1, 1)
        ' a) Count the cells containing a number.
        drCount = Application.Count(.Cells)
        ' Or:
        ' b) Count the non-blank cells.
        'drCount = srCount * scCount - Application.CountBlank(.Cells)
    End With
    If drCount = 0 Then Exit Sub ' no cell's meeting the criteria found
    
    ' 6.) Define the destination array ('dData'),
    '     a 2D one-based three-column array.
    
    Dim dData() As Variant: ReDim dData(1 To drCount, 1 To dcCount)
    
    ' 7.) Write the values from the source array to the destination array.
    
    Dim sr As Long
    Dim sc As Long
    Dim dr As Long
    
    ' Loop through the columns of the source array (skip row labels).
    For sc = 2 To scCount
        ' Loop through the columns of the source array (skip column labels).
        For sr = 2 To srCount
            ' a) Check if the current value in the source array is a number.
            If VarType(sData(sr, sc)) = vbDouble Then ' is a number
            ' Or:
            ' b) Check if the current value in the source array is not blank.
            'If Len(CStr(sData(sr, sc))) > 0 Then ' is not blank
                dr = dr   1
                dData(dr, 1) = sData(1, sc) ' write column label (first row)
                dData(dr, 2) = sData(sr, 1) ' write row label (first column)
                dData(dr, 3) = sData(sr, sc) ' write value
            'Else ' value doesn't meet the criteria; do nothing
            End If
        Next sr
    Next sc
    
    ' 8.) Write the values from the destination array to the destination range.
    
    With dfrrg
        ' Write.
        .Resize(drCount).Value = dData
        ' Clear below.
        .Resize(ws.Rows.Count - .Row - drCount   1).Offset(drCount).Clear
    End With

    ' 9.) Inform.

    MsgBox "Data unpivoted.", vbInformation

        
End Sub

CodePudding user response：

F2 has =INDEX($B$1:$D$1,MOD(ROW()-ROW(F$1)-1,COUNTA($B$1:$D$1)) 1)
G2 has =INDEX($A$2:$A$5,MOD(ROW()-ROW(G$1)-1,COUNTA($A$2:$A$5)) 1)
H2 has =INDEX($B$2:$D$5,MATCH(G2,$A$2:$A$5,0),MATCH(F2,$B$1:$D$1,0))

Replace $B$1:$D$1 with your column heading range for Customer columns, Replace $A$1:$A$5 with your row heading range for Product columns, Replace F$1 with the address of your Customer heading on the result range ( H$4?) Replace G$1 with the address of your Product heading on the result range ( I$4?)

Replace $B$2:$D$5 with your data range (where the volumes are)

You also need to adjust G2 to and F2 in the last formula in H2 (in my example). If this to go to J5 (in yours), change G2 to I5, and F2 to H5.

I hope this helps; if you still have difficulties tell me what your H5:J5 refers to: the first row of results, or the heading (Customer Name,Product Name, Volume)

You still calculate rows*columns to copy the formulae down. Also it does not remove blank cells (your Customer1 having no Product volume.

CodePudding user response：

I have figured it out using power query. This is really so easy. All I need to to is-

Step 1: Open the table in power query,

Step 2: Select the column Product name,

Step 3: Right click and Unpivot the other columns. And got the desired result.