View Full Version : Solved: Show top ten closest match amount

01-28-2013, 07:35 AM
Hi there Everyone!

Please try to help me if you can. I have two tables. Table A contains the risk drivers, Table B contains the Risk values.

I would like to assign the risk drivers to each Risk. Meaning that each risk has a value and then the risk drivers should be assigned to in a top 10 structure, so the closest value is the first, and the farthest value is the last.

Please see the attched spreadsheet where you can see it more clearly what I am looking for. In the spreadsheet I only assigned a top 3 risk driver table (under each risk).

In my VBA I already managed to code the calculations which calculate the numbers, but at this point I am stucked. I don't know how to make a closest match and top 10 sorting order.

Thank you in advance for your help!

01-29-2013, 01:34 AM
1. first sort Table A by values (largest to samallest)
2. do loop in Table B take value
3. filter Table A by Table B value
4. Do loop on Table A for first 10 values

02-06-2013, 04:06 AM
Finally I choose to sort all of the risk drivers in an descending order.

This is the key code row:

Selection.Sort Key1:=Sheets("Results").Cells(3, y + 1), Order1:=xlDescending