Help with this test buy

H i everyone, I'm a noob regarding excel and I have a question for you more advance users that maybe you can help me out:



I created this as a test buy in which articles will constantly change every time the document is open, or they press F9. To do this, the following formula is in "Random" sheet in A# column:



=INDEX(Some!A2:A18,RANDBETWEEN(1,ROWS(Some!A2:A18)),1)



It gathers info from "Some" sheet, and it works well. Now what I want is to avoid showing articles with stock zero, and this is the part that I don't know how to do it.



Any help, I appreciate it!



Thanks.

Attachment 767968

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new here, I will do it for you this time: https://www.mrexcel.com/board/thread...-zero.1196008/)

You left out (I think) one further requirement... that items selected should NOT repeat. So... non-zero stock non-repeating random selection generated by:

=LET(n,COUNTIF(Some!D:D,">0"),r,SEQUENCE(n),s,INDEX(Some!A:A,AGGREGATE(15,6,ROW(Some!$D$2:$D$32)/(Some!$D$2:$D$32>0),r)),INDEX(s,AGGREGATE(15,6,r/NOT(COUNTIF($A$1:A1,INDEX(s,r))),RANDBETWEEN(1,n-ROW(A1)))))

and then

=VLOOKUP($A2,Some!$A:$D,COLUMNS($A2:B2),FALSE)

to return the other columns.

or... just one place to change the range under consideration:


=LET(d,Some!$D$2:$D$100,n,COUNTIF(Some!D:D,">0"),r,SEQUENCE(n),s,INDEX(Some!A:A,AGGREGATE(15,6,ROW(d)/(d>0),r)),INDEX(s,AGGREGATE(15,6,r/NOT(COUNTIF($A$1:A1,INDEX(s,r))),RANDBETWEEN(1,n-ROW(A1)))))

Argghh! Must I?

The basic formula at the heart of it is (not tailored to your sample... it's from a file I have here:

INDEX(O:O,AGGREGATE(15,6,ROW($O$1:$O$7)/Additional conditionNOT(COUNTIF($N$1:N1, INDEX(O:O,ROW($O$1:$O$7)))), RANDBETWEEN(1,8-ROW(N1))))

Cyan: looking at this range...

Red - return the row number

Green: Only those that have NOT been returned in an earlier cell

Blue - generate a random number between 1 and 7 (in effect the number of values) dimisnishing by 1 on every row.

Black and return the corresponding value from column O.

Starting from there, I adjusted the ranges to suit your set-up and added another condition (purple text) to the effect that the value in D>0.

The rest id just twiddly bits to make sure that the formula runs sommothly. LET just defines pharases that are repeated... but at its heart is:

INDEX(A LIST OF NON-ZERO STOCK ITEMS,AGGREGATE(15,6,AN ARRAY OF NUMBERS 1,2,3, to the total /NOT(COUNTIF($A$1:A1,A LIST OF NON-ZERO STOCK ITEMS)),RANDBETWEEN(1,n-ROW(A1)))))

Clear as mud??

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

Best thing it to cut the sample down to a very few rows and then use

Formulas/Formula auditing/evaluate formula

to step through the process. It has to be a very few rows...as the windo is non-resizable and (painfully) small to read.

Originally Posted by Fluff13

Another option would be

Formula:

Please Login or Register  to view this content.

which will return 10 items

I'm about to vanish for the night and haven't time to check... Fluff... will that also prevent the return of duplicate values??

Thanks. On phone now. I'll look and learn tomorrow.

Thanks for the time on explaining this, I have another question: If an article have more than one location, how can I show all the locations?

Show us what you mean by that on a sample sheet.

Confused. I assume Name = Product. Take "Name" Four in random. It appears 5 times on sheet "locations", but all at teh same location. So, what would you expect to see? Where would you expect to see it??

please amend with expected results!!

I am about to leave to get my neck stretched (literally) but will be back later... providing I survive the physio's assault.

Does this work for you??

Hi,

It partially does the trick, shows all locations, but for some it repeat it, ideally you just want to see all of them without repetition. Also, is there a way to show it vertically, not horizontally?

Example:

A# A.Name Stock Location 1 Location 2 Location 3 Location 4 Location 5
654 Dos 423342 ndsvjknahr2h asd1313
4567 Four 6 42pjhlfahslh 42pjhlfahslh 42pjhlfahslh 42pjhlfahslh 42pjhlfahslh

Gracias!