# What stock is not where for too many stores ?

1. ## What stock is not where for too many stores ?

I am trying to find what stores has stock equal to zero

And thus formula on L2 to drag down where the result should look as follows

1 & 2 & 3

Where the numeric is a store number picked from raw sheet 2 the stock table

2. ## Re: What stock is not where for too many stores ?

=if(or(e2=0,f2=0,g2=0,h2=0),true,"")

3. ## Re: What stock is not where for too many stores ?

This is not correct , raw data is on sheet 2

It likely looks to be one big formula

4. ## Re: What stock is not where for too many stores ?

Maybe it will a formula with and & functions , helper will be required maybe

5. ## Re: What stock is not where for too many stores ?

Can you explain why 1, 2, 3 is the result you want? What you have in your sheet doesn't make sense to me...

6. ## Re: What stock is not where for too many stores ?

Glenn I am sure you will save my day , 1 2 3 are locations ( see column E F and so on ) on sheet 2 which is the stock table

I am trying to find if stock has been distributed to all stores for maximizing sales

7. ## Re: What stock is not where for too many stores ?

The expected result for item 1 is

1 & 2 & 3 & 4 & 5 & 6 & 9 10 11& 14 & 15 & 16 & 18 & 23 & 24 & 26 27

8. ## Re: What stock is not where for too many stores ?

While store 25 is the distribution store

9. ## Re: What stock is not where for too many stores ?

GLEN you may need tricky helpers maybe , i doubt one formula will do that

10. ## Re: What stock is not where for too many stores ?

The only way I could get the answer you wanted was to use a UDF ("borrowed" from Chip Pearson) - which means you must use a macro enabled sheet; and to enter N/A (or similar) in the non-operating stores. See Module 1 for the code needed.

the basic formula (array entered) is = STRINGCONCAT("separator", criteria range, range to return). the enxt problem was that the order of the codes was different in the two sheets; so I had to use a mix of MATCH and INDIRECT to get the right answer against the right store. Final formula (array entered) was:
Formula:
`Please Login or Register  to view this content.`

11. ## Re: What stock is not where for too many stores ?

Glen this is beyond AWESOME , before I close the thread , will it work on huge data say 30,000 rows

12. ## Re: What stock is not where for too many stores ?

There's only one way to find out, makinmomb... try it & let me know. The INDIRECT function will slow it down significantly, though.

13. ## Re: What stock is not where for too many stores ?

Okay noted , Maybe I will trick it with less data

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1