Macro to do random selections based on certain criteria
I please need help cleaning up or totally changing a macro I have been using. This will be a long post - I apologize in advance.
I'll try and put current functionality in a nutshell:
The purpose is to provide a user with an Excel file with RANDOM account numbers that meet certain criteria. There are about 50 different criteria types (e.g. Account older than 6 months with a debit balance and 2 specific insurance products selected and their chosen language as Zulu), and then each criteria type must show one RANDOMLY selected account number PER product (of which there are about 20).
I load almost a million records from text files into a QV table. it contains full account detail.
I then created a bookmark for each criteria type and product combination (back then there were fewer criteria and products but I still ended up with about 190 bookmarks. took me for ever).
Then I created a macro which selects one bookmark and sends all possible account numbers from a table box to an excel file (column A). There can be 20 accounts or 250,000 accounts meeting the bookmark selections. In cell B1 of the Excel file the macro then adds a formula which selects one random value from column A. The macro then keeps this one random value and deletes the entire Account list from Column A.
And then I repeat the macro code for each and every bookmark I have, but each time I change the bookmark name and the location of the excel formula, so eventually I end up with my random selections. Right at the end the macro adds the bookmark descriptions to column A and the Product Names as headers. See the Excel Sample image attached.
The macro looks like this, but it is repeated for each bookmark (the red parts are changed for each repetition):
Currently this macro runs an hour or more. It is almost 4,000 lines. Plus the code is a mess, and each time the users want to add or change criteria, or when new products are added it takes me hours to update this.
I need to clean this up somehow.
I'd like to change my macro to do the following, but have no idea how:
Select each bookmark that exists and follow my above code, and then just loop through each and every bookmark and repeat the same step, but each time the values in red also would need to change. Plus I need to cater for the different products, which act almost like a dimension split.
Best case scenario is to have a straight table in QlikView with Product as dimension and each Criteria Type as an expression, giving me one random account number - but this doesn't seem possible. The QlikView rand() function doesn't seem able to meet my requirements.
I hope the attached image of the end result helps to make this clear, and I hope there is a better way of doing this.