[Solved] Make a list from a matrix

[emflorence.br]

Hello Exceldemy team!
I need to build up a spreadsheet that display the quantity of the items that i have in my stock around 10 warehouses. For a brief example, lets say that I have lines with the warehouses and columns for the items. The matrix contains quantity of all of them.

So it would be a list spreadsheet like this:
Warehouse Water Coke D_Coke Bread Milk Macaroon Eggs Meat Truffles
A 0 0 1 0 1 0 5 1 0
B 2 1 0 0 0 0 0 1 2
C 5 0 1 0 1 0 1 1 4
D 1 0 9 2 1 4 2 3 0
E 3 0 1 1 1 0 0 1 6
F.... and it goes like this.

Than i need to crate a drop down menu with the warehouse I want to verify stock and I would like to display in a text box the quantity of the items that I have in that warehouse, for example:
Warehouse B: 2 Water; 1 Coke; 1 Meat; 2 Truffles.

Can you help me? Do I need a macro for that?

 

[Seemanto Saha]

Hello emflorence.br,
Thanks for sharing your problem with us. I understand that you want to build up a spreadsheet that displays the quantity of the items that you have in your stock for the warehouse selected through a dropdown.

From your given data, I created the following dataset:

To add a dropdown list in Cell B13, select the cell >> go to the Data tab >> click on the dropdown menu of Data Validation >> select the Data Validation option.

​

From the Data Validation user form, go to the Settings tab >> Set the Allow option to List >> Set the Source for the list to range A2:A11.

Next, right-click on the sheet name tab (i.e. Leaf Bar) and select the View Code option.

​

Insert the following VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Set ws = ThisWorkbook.Worksheets("Data")
    Set Rng = ws.Range("A1:J11")
    
    Dim i As Integer
    Dim j As Integer
    Dim outString As String
    
    If Target.Address = "$B$13" Then
        If ws.Range("B13") <> "" Then
            outString = "Warehouse " & ws.Range("B13") & ": "
            
            For i = 2 To Rng.Rows.Count
                If Rng.Cells(i, 1).Value = ws.Range("B13") Then
                    Exit For
                End If
            Next i
            
            For j = 2 To Rng.Columns.Count
                If Rng.Cells(i, j).Value <> 0 Then
                    outString = outString & Rng.Cells(i, j) & " " & Rng.Cells(1, j).Value & "; "
                End If
            Next j
            
            outString = Left(outString, Len(outString) - 2)
            
            MsgBox outString
        End If
    End If

End Sub

Now, go to the datasheet and select any value from the dropdown list in Cell B13. A Message Box with the required output will pop up.

​

If you change the warehouse value in Cell B13, you will get a different Message Box. However, if you set Cell B13 to empty or change any cell other than B13, there will not be any Message Box.

The workbook I used for demonstrating your problem is attached below. Hopefully, I was able to resolve your problem. Let us know your feedback.

Regards,
Seemanto Saha
ExcelDemy

 

[emflorence.br]

That solved my problem. Thank you very much!