VB code to Copy specific column from one sheet and paste into another based on a criteria

Hi Forum,
I have a worksheet called as "data". The data worksheet has columns called as "Factory", Comodity, Designation,Current Supplier, Current Supplier cost,landed factor (I multiply the landed factor with the supplier cost to get the landed cost of that commodity for that factory). The total number of rows might exceed 1500. Same designation can be bought by many factories.
Now I want to
1.) for a given designation the minimum landed cost (landed cost = Supplier cost * Landed factor) and the supplier offering the minimum cost. Let us call it Best landed cost and Best supplier. Note based on the landed factor a supplier can be best for one factory but may not be best for some other factory. I have done it with the help of INDEX and MATCH function, but it makes the calculation very slow and takes a lot of time. I was wondering if a VB code will do it faster??
2.)I have another sheet called as Table, In this sheet I want that when I select a particular factory it gives me the details of just that factory and not of others containing data as Name of Factory, Commodity, Designation, Current supplier, Current cost, Best supplier (in terms of landed cost) and best cost.

I am attaching a file with dummy data so that colleagues in the forum can understand my requirement better.

I hope with such great programmers in the forum my problem will be solved very quickly.
Best regards
Amit Bakshi