LinkBack URL
About LinkBacks
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
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
=if(or(e2=0,f2=0,g2=0,h2=0),true,"")
This is not correct , raw data is on sheet 2
It likely looks to be one big formula
Maybe it will a formula with and & functions , helper will be required maybe
Can you explain why 1, 2, 3 is the result you want? What you have in your sheet doesn't make sense to me...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
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
The expected result for item 1 is
1 & 2 & 3 & 4 & 5 & 6 & 9 10 11& 14 & 15 & 16 & 18 & 23 & 24 & 26 27
While store 25 is the distribution store
GLEN you may need tricky helpers maybe , i doubt one formula will do that
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.
I didn't follow your comment about store 25...
Glen this is beyond AWESOME , before I close the thread , will it work on huge data say 30,000 rows
There's only one way to find out, makinmomb... try it & let me know. The INDIRECT function will slow it down significantly, though.
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
Register To Reply
Bookmarks