I have an excel workbook with several sheets. I am trying to make a randomizing procedure from pre configured lists and copy the top result to a different sheet.
In sheet ("Config") i have several small lists 1-15 items long side by side from column A - Column Z.
I wrote a macro that stinks I am hoping to improve. What it does is copies the items in range A3:A18 down to A25 (empty area of the spread sheet). Then in column B next to the copied down list I have a =rand() formula that runs down the length and then I do a sort by Column B within that range. This is so I can randomize the list in column A:25 and below. Then I copy the item that goes to the top in A25 to sheet ("Output") location A1. Then it clears the sort area.
Now I am trying to figure out how to loop this. So the next loop would be to take the list in sheet ("Config") column b > copy the list from B3:B18 down to the same area A25 (Or can go to B25) > do a sort procedure > choose the item that rises to the top and then copy that to sheet ("Output") A2 and so on.So it will loop and sort on ("config") across columns from left to right and put the out come of each sort on the sheet ("Output") in column A row 1 though 26. Then the loop will end.
Now one issue is that each list has different lengths so there cannot be any blank spaces copied to the "Output" sheet. I don't want disturb any of the original list as I will update these from time to time manually but will not go over 15 entries per list.
Now my procedure I built is aweful using the macro builder. Now I see I will have to do this for 26 times if I go the way I am doing and my code will be 25 pages long.
I don't know how to loop it at all.
I appreciate any help
Thanks you.
In sheet ("Config") i have several small lists 1-15 items long side by side from column A - Column Z.
I wrote a macro that stinks I am hoping to improve. What it does is copies the items in range A3:A18 down to A25 (empty area of the spread sheet). Then in column B next to the copied down list I have a =rand() formula that runs down the length and then I do a sort by Column B within that range. This is so I can randomize the list in column A:25 and below. Then I copy the item that goes to the top in A25 to sheet ("Output") location A1. Then it clears the sort area.
Now I am trying to figure out how to loop this. So the next loop would be to take the list in sheet ("Config") column b > copy the list from B3:B18 down to the same area A25 (Or can go to B25) > do a sort procedure > choose the item that rises to the top and then copy that to sheet ("Output") A2 and so on.So it will loop and sort on ("config") across columns from left to right and put the out come of each sort on the sheet ("Output") in column A row 1 though 26. Then the loop will end.
Now one issue is that each list has different lengths so there cannot be any blank spaces copied to the "Output" sheet. I don't want disturb any of the original list as I will update these from time to time manually but will not go over 15 entries per list.
Now my procedure I built is aweful using the macro builder. Now I see I will have to do this for 26 times if I go the way I am doing and my code will be 25 pages long.
I don't know how to loop it at all.
I appreciate any help
Thanks you.
