I have a list of ~50k loan records. I am regularly asked to create a random sample of this data while targeting specific overall weighted averages. I have no idea where to begin, but in theory I would like to have a macro where I can specify a total loan amount (e.g. $1,000,000), a weighted average credit score (e.g. 720) and a weighted average interest rate (e.g. 4.5%) and have the macro select a list of loans that result in a total balance of $1,000,000 with those target weighted averages (with the ability to set mins and maxes for different attributes as well).
In my head it is a giant conditional goal-seek. Has anyone ever put something like this together or have any idea of how they would approach this? Is there an existing tool in Excel that would do this or is VBA the best path forward?