Selection Process finding "x"s from within 7 columns

[curbster]

Hi I have a process in which I have to create a selection without bias. There are 9 categories in this selection process. The 1st and 2nd category already have code and they work differently from the rest. For the 3rd through the 8th categories I have to choose one from the 3rd category, then one from the 4th, etc. After the 8th category I start back at the 3rd and go to the 4th, ect. It ends when cell A1 reaches 30%.

Its a little difficult to explain but the process is simple enough to understand once you see it. I have attached an example of the process. I have also started the code and left explanations within the code.

Please Login or Register  to view this content.

I'm hoping there are just a few lines of code that I can loop until the cell A1 reaches 30%.

Please help!
Thank you!
Curbster

[Paul]

Hi Curbster,

A few questions...

1. In your pseudo-code, you say for each part of the loop "Start at cell D2", "Start at cell E2", etc. but your example spreadsheet shows gray boxes for the first round of selection don't follow that pattern. It looks like you start at D2 and find the first "x" below that, then you go over to column E in that row and find the first "x" from there downward, then move to column F from that row and find the first "x". If you truly wanted to start at the top of each column and find the first "x" from the top, I would expect to see D32, E5, F2, G2, H2, I2 and J2 selected in the first round. Am I correct in my observation?

2. What happens when you get to the "last row" in a column and 30% has not yet been reached? Do you start back at the top of that column? (This assumes my observation in question 1 was correct, and that you're not simply starting at D2, E2, F2, etc. every step through the loop.

Thanks.

[curbster]

Paul,
you are exactly right! My mistake. So once a selection is made, go to the next column (same row) and head downwards. Lets say I just selected a 6, I would go over to column 7, and then down to the first x. If I hit the bottom and have not found an x in that column, I start back at the top (in the same column) until I find the next 7. If there are no 7's left, I go on to the next column, column 8.

Thanks for helping me clear this up. I'll edit it above as well.

is it even possible to write code to do this?
Thanks!

[curbster]

any help on this?

thanks!

[ravishankar]

Hi
try the macro, it gave 29.58%. may be I am missing something.
Ravi

[curbster]

I appreciate your help! Its close. Is there any way to get it to start back at the top after every round? For example column 3 starts the next X down from the top instead of the next x down from the last x in column 8?

Also, I tried deleting the selection numbers and running the code, it only got me to 22%. I think it is because it is not starting over at the top. If I reach the bottom before I finish an 8 selection, I start back at the top and grab the first x in column 8 (unless it has already been selected by another number). I do this for all columns. Another example, if after the selection has started and I run out of 3's, I go on to the 4's. So I would start at 4, then go right to 5, 6, 7, 8, and back to 4, 5, 6... etc.

I tried to figure out the code but I'm no good at creating the code yet. Thank you so much for your help!

[curbster]

Ravi, here is the sheet with the expected results. I apologize if I was unclear on my original thread. Thanks for taking the time to help out!
Curbster.

[curbster]

Anyone avalible to help with this? Thanks!

[curbster]

I'm not sure if my post is showing up on the page since the original post was from last week, but I'm still in need of some help if anyone knows how to do this. Thanks!

[StephenR]

Looks interesting, but in your example A1 is already over 30%. Can you post another example?

[curbster]

Ok, I got most of it working for how I need, now it just needs to keep looping back to the top until cell A1 equals around 32% (currently it only reaches 13.52%).

once you have run the macro, see the comment in cell J141 to explain.

Please Login or Register  to view this content.

thanks for your help!

[StephenR]

I'm a little confused about that code works at all because originally you said:

'start at cell D2 and go down to the first "x" in the column.
'If column B of same row contains a number go to next "x" below in column D
'if column B of same row equals nothing, place a "2" in column B

But in the code just posted you have this section

Please Login or Register  to view this content.

which means that there will always be a value in B whenever D is 'x'. Am I missing something?

[curbster]

you are correct, I realized that I preselect rows 1 and 2 (or, columns C and D) and those are done first and remain untouched. I had to change the code a little to reflect the selection process to only choose columns 3-8.

If the cell in column B (SEL column) contains a number, then go to next row and continue searching down. So as far as the code is concerned for onex and twox it works perfectly for what I need.

the code I can't figure out is below that. I have come up with a loop but it is not working the way I need it to. It overwrites the 1 and 2 columns and doesn't seem to stop where it should, at 32%.

Please Login or Register  to view this content.

[StephenR]

I have written some code and got it up to c19%, but it appears that for every x in col F, col B has a number. Should it then jump to the next column? Are you sure that this does have a solution!?

[curbster]

Right, if there are no more 3's for example, the selection would continue to 4-8, if it randomly runs out of 7 to select, then it should keep selecting from the remaining columns.

Since this is dummy data and i have changed the numbers in column W (to see what the macro would do when it ran out of x's) it may not reach 32%. The formula in A1 should be divided by the sum of column W.

ex. =SUM(A2:A150)/1242557
where 1,242,557 equals sum of W

Like I said I did change the numbers around to see what errors I would get with the macro. Feel free to change the formula to reflect the "/W:W"

[StephenR]

This code, which replaces your current loop, stops at 31.21%. Give it a thorough check to see if it works as it should as it is a little confusing.

Please Login or Register  to view this content.

[curbster]

Ok, I've attached a new sample spreadsheet for trial with this macro. Basically I took the old one and duplicated it 3 times to add rows.

Macro seems to finally be stopping around 30%! thank goodness!

*****One problem is it is still skipping over columns 1 and 2. How do I get the macro to recognize this? If I do the selections for 1 and 2 manually before I run the macro it works perfect, meaning the macro does not override the 1's and 2's which is great! I guess I'm going to need help debugging.

I thought about moving the 1 and 2 code to after the loop (at the bottom of the code) but that might mess up the order of the selection, so it does need to come first.


*****UPDATE: Since posting this message I have done some troubleshooting and found the problem. There were "End For" statements that were stopping those processes before the macro could work. See the attachment as the correct code. It has a button to make it a one click wonder. thanks Stephen this will save HOURS!!!

see working code below:

Please Login or Register  to view this content.