Hello.

I have this table.
PartNumber, SupplierNumber, MaterialController, StockOnHand, Demand

The table contains approximately 8000 rows.

In another sheet I have a the statistics calculations.
I want to look at the statistics about each Material controller and how many shortages (Demand-StockOnHand) they have. I also want to know how the shortages are divided by the suppliers connected to each material controller.

If Material controller number 2 have 10 part numbers and 6 of those part numbers have shortages, how many suppliers are affected? Lets say that Material Controller number 2 have 5 suppliers connected to him and only supplier number 4 are not affected.
The result of this loop would be that 4 suppliers are affected by the shortages. I want the result to appear in AX7.

I hope someone can help me with a loop that does this:

1. Loop through the Data sheet from row A35 to A15000 where the Material Controller=(Statistics sheet A7).
2. For each part number where Demand minus StockOnHand = <0
3. Create new array with part numbers.
4. Add supplier number if it has not already been added to the newly created array.
5. When loop is finished all affected suppliers are added to the array.
6. Count how many suppliers that are in the array.
7. Write the number of unique suppliers in the Statistics Sheet AX7.

Can someone please help me create this vba code?

I want to start the function with this code or similar:
=Get_affected_suppliers($A7)

I hope someone with much skills in VBA can help me.

Thanks in advance.

/Anders