Data from multi row and column in Excel table ignoring empty cells.

[husoi]

Hi Everyone and thank you for looking,

I tried to find a post with what I need but doesn't gets me anywhere close to what i need...


I have a table that is organized as follows:

• rows (first column) have equipment names i.e. pumps, valves, pipes, etc.
• columns are the locations where the equipment is used.

In the interception I will have the name of the supplier for the that piece of kit for that location.


What I need:
In another tab, the user will choose the equipment from a dropdown list, then Excel will show:
column 1: who the supplier is;
column 2: which locations have been supplied.


Because the table is 200 columns long and over 50 rows (and growing) I need Excel to ignore cells with blank information ("" as the table is populated with formulas gathering information from elsewhere, note that "" can be replaced with 0 if more convenient)


To make it more complicated, the same supplier can be involved in more than 1 location and each location probably will have more than 1 type of equipment. the likes of pumps and pipes to same location supplied by different companies.


I'm open for suggestions for some formulas or any other process, the likes of forms or VBA codes that will do this.

I have started a form for this and the VBA looks like this (I have managed to get the dropdown lists to work but that is as far I can go):

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Sub MechButton_click()
' Option button for Mechanical
options
EndSub

Sub ElectButton_click()
' Option button for electrical
options
EndSub

Sub options()
' Display equipment list as per option buttons

ComboBox1.Clear

SelectCaseTrue

Case MechButton
ComboBox1.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("MECHANICAL").RefersToRange)

Case ElectButton
ComboBox1.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("ELECTRICAL").RefersToRange)

EndSelect

(Got stuck from here)

EndSub

</code>
Thank you very very much for your help

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;"></code>Hu