Multiple Custom sorting with addition of a new column in between sort

DrPacman07

New Member
Joined
Apr 27, 2015
Messages
1
Hi Sir/Madam,

Good day!

I am currently working on a scenario ranking and assignment of the probabilities process wherein I need to sort an array of multiple columns (smallest to largest) based on a specific column. After which I need to add a new column (the set of probabilities) to be included in the array of columns for sorting. These processes are repeated until the array of columns are sorted X times ( X is based on a specific predetermined columns).
[TABLE="width: 859"]
<colgroup><col span="8"><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]Example (smaller scale)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"](Initial Data)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Probability Generator[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="colspan: 3"]Probability Generator Factors[/TD]
[TD]0.5431[/TD]
[TD]0.4839[/TD]
[TD][/TD]
[TD]0.5431[/TD]
[TD](will change for each Column depending on the factors)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]120[/TD]
[TD]90[/TD]
[TD][/TD]
[TD]120[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="colspan: 3"]Data[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0.079[/TD]
[TD]0.057[/TD]
[TD]0.005[/TD]
[TD]0.01[/TD]
[TD]0.018[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0.09[/TD]
[TD]0.007[/TD]
[TD]0.011[/TD]
[TD]0.033[/TD]
[TD]0.07[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0.011[/TD]
[TD]0.065[/TD]
[TD]0.064[/TD]
[TD]0.043[/TD]
[TD]0.072[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]0.01[/TD]
[TD]0.067[/TD]
[TD]0.068[/TD]
[TD]0.023[/TD]
[TD]0.074[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]First Sort Based on column 1 then add the generated probability[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Probability Generator[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="colspan: 3"]Probability Generator Factors[/TD]
[TD]0.5431[/TD]
[TD]0.4839[/TD]
[TD][/TD]
[TD]0.5431[/TD]
[TD](will change for each Column depending on the factors)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]120[/TD]
[TD]90[/TD]
[TD][/TD]
[TD]120[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="colspan: 3"]Data[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0.079[/TD]
[TD]0.057[/TD]
[TD]0.005[/TD]
[TD]0.01[/TD]
[TD]0.018[/TD]
[TD]0.3[/TD]
[TD][/TD]
[TD]0.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0.01[/TD]
[TD]0.067[/TD]
[TD]0.068[/TD]
[TD]0.023[/TD]
[TD]0.074[/TD]
[TD]0.4[/TD]
[TD][/TD]
[TD]0.4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0.09[/TD]
[TD]0.007[/TD]
[TD]0.011[/TD]
[TD]0.033[/TD]
[TD]0.07[/TD]
[TD]0.1[/TD]
[TD][/TD]
[TD]0.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]0.011[/TD]
[TD]0.065[/TD]
[TD]0.064[/TD]
[TD]0.043[/TD]
[TD]0.072[/TD]
[TD]0.2[/TD]
[TD][/TD]
[TD]0.2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]Second Sort Based on column 2 then add the generated probability[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](X)[/TD]
[TD]Probability Generator[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="colspan: 3"]Probability Generator Factors[/TD]
[TD]0.5431[/TD]
[TD]0.4839[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.4839[/TD]
[TD](will change for each Column depending on the factors)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]120[/TD]
[TD]90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]90[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="colspan: 3"]Data[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0.079[/TD]
[TD]0.057[/TD]
[TD]0.005[/TD]
[TD]0.01[/TD]
[TD]0.018[/TD]
[TD]0.3[/TD]
[TD]0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0.09[/TD]
[TD]0.007[/TD]
[TD]0.011[/TD]
[TD]0.033[/TD]
[TD]0.07[/TD]
[TD]0.1[/TD]
[TD]0.2[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0.011[/TD]
[TD]0.065[/TD]
[TD]0.064[/TD]
[TD]0.043[/TD]
[TD]0.072[/TD]
[TD]0.2[/TD]
[TD]0.2[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]0.01[/TD]
[TD]0.067[/TD]
[TD]0.068[/TD]
[TD]0.023[/TD]
[TD]0.074[/TD]
[TD]0.4[/TD]
[TD]0.1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.1[/TD]
[/TR]
</tbody>[/TABLE]
Note:
*The probability generator factors will be pasted below (X) to generate new probabilities
*This is just a small scale example of the problem, normally we do multiple times (100x)

I hope you can help me do a macro program to make my life easier. :)

Thanks A lot.
 
Last edited:

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top