• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

found and seek

Lawrence Dodge

Lawrence Dodge

Member
Hi all--

I am working on a large MONTHLY inventory sheet with 400+ rows. In column AM, a letter is displayed based on amounts found elsewhere in the workbook. "A" for over 10,000, "B" for 5,000 to 10,000, "C" for under 5,000 and "D" for under 100. This is a dynamic letter which changes month to month.

What I am trying to do is randomly pick so I can count all the "A"s weekly, the "B"'s biweekly, the "C"'s and "D"'s monthly. This will end up being about 6 - A each day, about 3 B done every day, about 15 C's and 3 D's being counted every day.

I would prefer not to use VBA, as I am no go at editing VBA. If you have an explicit code that would work, I could use that. Thanks in advance.

LCD
 
Can you explain more on your 2nd paragraph? I don't quite understand what you really want. thx.
 
How to "randomly pick" AND "count all"? Yeah, your question is a little difficult to understand, but the good news is that this looks pretty simple. Define this a little better, and we can get you an answer quickly.
 
Ok, let me see if I can decribe the scenario a bit better. We have four different categories of inventory (A,B,C,D)(though there is NOT a set number of any category-- each category gets refreshed at the beginning of the month). The categories are based on usage. What we want to do is count all of the A's weekly, the B's biweekly and the others monthly.

Now let's just work with the A's. Instead of counting everything on one day of the week, we thought it would be a good a idea to randomly pick about 6 A's each workday (Monday through Friday) and manually count those, then pick another group (with no repeats of previously counted items)of A's the next day until all the A's are counted for the alloted time period. Then do the same for the B's, C's, D's.

Hopefully by doing this, it will save time / effort from dedicating one day to counting ALOT of things.

I know that's about as clear as mud, if you need any more information, just let me know. I GREATLY appreciate your help in this matter.

LCD
 
Correct me if I misinterpret your question. you want Excel to randomly pick something from a column at given time, right?


So let's say line 1 is header and the list starts on row 2 down.


=RANDBETWEEN(2,n) where n = the last line on column A


repeat this 6 times or create 6 of this in different cells. In case you get a repeated number, simply hit F2 key and rerun it again.


E.g you have 999 product usage in A.


So the formula would be: = randbetween(2, 1000) and the result is, say, 374. Your audit product usage in column A is on line 374. hit F2 key and see what's the next line to show up. If by chance 374 shows up again, hit F2 key again. if all 6 re-run come up with the same number on a 1000 lines, could you please let me know? I'll need to buy a lottery ticket. ;)


If this is not want you need, then I must have mis understood your need and I apologize in advance.
 
Hi Fred--

Well that's a start; as it stands right now we have a vlookup finding all the A's in the column and then have Excel count how many there are then we manually divide that number by 5 (Mon - Fri) and go out to the warehouse to count those A items. Can we somehow tie the Vlookup together with randbetween and count if?

We need to have a way to make sure everything gets counted and not overlooked. Not to difficult with the A category with only 30ish products, the C's though have hundreds and only get counted monthly.

Thanks for your thoughts.

LCD
 
You must log in or register to reply here.
Back
Top