Repeat formula until is not Zero`

[darkybot]

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.

 

[Fluff]

Hi & welcome to MrExcel.
How many rows do you want the formula to return?

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Help with this test buy
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

As you haven't answered my question, this will return 10 items

=LET(f,FILTER(Some!A2:D100,Some!D2:D100>0),INDEX(SORTBY(f,RANDARRAY(ROWS(f))),SEQUENCE(10),{1,2,3,4}))

 

[darkybot]

Hi, sorry for not posting the cross-link, Im new to this type of format. This works wonderful, THANKS!!! quick question: How can I show multiple locations for one article keeping the format of showing 10 different ones? Let's say Article: Sun has 3 locations, which means 3 rows on "Same" sheet showing "Sun" with 3 different locations and I want all of them to be shown plus 9 other articles.

 

[Fluff]

How about

=LET(f,FILTER(Some!A2:D100,Some!D2:D100>0),i,INDEX(SORTBY(f,RANDARRAY(ROWS(f))),SEQUENCE(10),{1,2,3,4}),FILTER(Some!A2:D100,ISNUMBER(MATCH(Some!C2:C100,INDEX(i,,3),0))))

 

[darkybot]

As you can see "Adios" only shows 2 times, even though it has 5 locations. The idea is to see all locations for that article and still counts as 1 so it can show 9 more for a total of 20 different articles, or in my case 20. (I set it up for 20). How can I do that?

This is one sheet "Same", "Adios" has 5 locations, but only one shows

 

[darkybot]

How about

=LET(f,FILTER(Some!A2:D100,Some!D2:D100>0),i,INDEX(SORTBY(f,RANDARRAY(ROWS(f))),SEQUENCE(10),{1,2,3,4}),FILTER(Some!A2:D100,ISNUMBER(MATCH(Some!C2:C100,INDEX(i,,3),0))))

Still doesn't show all locations:

 

[Fluff]

Ok, I misunderstood what you wanted. Try

=LET(f,FILTER(Some!A2:D100,Some!D2:D100>0),i,INDEX(SORTBY(f,RANDARRAY(ROWS(f))),SEQUENCE(10),{1,2,3,4}),FILTER(Some!A2:D100,ISNUMBER(MATCH(Some!B2:B100,INDEX(i,,2),0))))

 

[darkybot]

Ok, I misunderstood what you wanted. Try

=LET(f,FILTER(Some!A2:D100,Some!D2:D100>0),i,INDEX(SORTBY(f,RANDARRAY(ROWS(f))),SEQUENCE(10),{1,2,3,4}),FILTER(Some!A2:D100,ISNUMBER(MATCH(Some!B2:B100,INDEX(i,,2),0))))

This works great, thanks, last question: Sorry but your formulas are mindblowing how well they work haha. In "Some" sheet Im taking the location from a 3rd sheet call "Locations" and it only retrieves one, any idea on how to show all locations?

"Same" sheet

"Location" sheet