# [Solved]Make a list from a matrix

#### emflorence.br

##### New member
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?

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

#### Attachments

• Make a list from a matrix.xlsm
17 KB · Views: 2
That solved my problem. Thank you very much!

Members online
0
Guests online
9
Total visitors
9