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
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