SanFelippo
Board Regular
- Joined
- Apr 4, 2017
- Messages
- 124
Hello everyone,
I have a dataset that is going to come every month. This data will come over with just two data fields in Columns A and B (Application Number and Channel).
Channel has 8 different potential values:
On another tab, we have sample selection methodology worked out that determines what number of applications need to be pulled for each channel's sample. It's all formula based, but essentially we end up with something like this:
My question is, how can I get excel pull a random sample from this data set for each area? So, using the numbers above as an example, I would need the formula or macro basically to go look at the entire data set and pull a random sample of 1 Business Banking loan, 6 Community RM loans, 2 Corporate loans, etc.
I know this could sorta be done by using the =rand() function and manually sorting and selection the sample, but this is going to be used by some people who aren't exactly excel savvy, so I am trying to figure out a way to build this so all they need to do is drop the new data into the Monthly Data tab each month when we need to pull a sample, and then on another tab called "Sample" it would automatically generate the sample they need to review.
In an ideal world the Sample tab could look something like this (xxx = an application number, where the number of "xxx" in each area is based off the Sample Count that was calculated for each area) and Excel is automatically and randomly selecting which apps are included and listing those app numbers on the "Sample" tab.
I was thinking this could maybe be coded into a macro, which could then be linked to a button that could be put on the Sample tab. Then the process would simply be:
1. Get the new data for the month
2. Paste it in Tab "Monthly Data" in columns A and B
3. Sample Size for each Channel is automatically calculated on tab "Sample Selection" (This is already done).
4. Move over to "Sample" tab, press the Macro Button that would have excel will run the macro which should look at the Sample Selection tab to determine the size for each channel, and then pull a random sample of each Channels determined size and list the app numbers.
Below I've included some screen shots of what the different tabs look like:
Monthly Data Tab
Sample Selection Tab
Sample Tab (Subject to change depending on how this can be coded)
Any help would be appreciated!
Thanks!
I have a dataset that is going to come every month. This data will come over with just two data fields in Columns A and B (Application Number and Channel).
Channel has 8 different potential values:
On another tab, we have sample selection methodology worked out that determines what number of applications need to be pulled for each channel's sample. It's all formula based, but essentially we end up with something like this:
My question is, how can I get excel pull a random sample from this data set for each area? So, using the numbers above as an example, I would need the formula or macro basically to go look at the entire data set and pull a random sample of 1 Business Banking loan, 6 Community RM loans, 2 Corporate loans, etc.
I know this could sorta be done by using the =rand() function and manually sorting and selection the sample, but this is going to be used by some people who aren't exactly excel savvy, so I am trying to figure out a way to build this so all they need to do is drop the new data into the Monthly Data tab each month when we need to pull a sample, and then on another tab called "Sample" it would automatically generate the sample they need to review.
In an ideal world the Sample tab could look something like this (xxx = an application number, where the number of "xxx" in each area is based off the Sample Count that was calculated for each area) and Excel is automatically and randomly selecting which apps are included and listing those app numbers on the "Sample" tab.
I was thinking this could maybe be coded into a macro, which could then be linked to a button that could be put on the Sample tab. Then the process would simply be:
1. Get the new data for the month
2. Paste it in Tab "Monthly Data" in columns A and B
3. Sample Size for each Channel is automatically calculated on tab "Sample Selection" (This is already done).
4. Move over to "Sample" tab, press the Macro Button that would have excel will run the macro which should look at the Sample Selection tab to determine the size for each channel, and then pull a random sample of each Channels determined size and list the app numbers.
Below I've included some screen shots of what the different tabs look like:
Monthly Data Tab
Sample Selection Tab
Sample Tab (Subject to change depending on how this can be coded)
Any help would be appreciated!
Thanks!
