+ Reply to Thread
Results 1 to 13 of 13

What stock is not where for too many stores ?

  1. #1
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2010
    Posts
    3,247

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

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,134

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

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

  3. #3
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2010
    Posts
    3,247

    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. #4
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2010
    Posts
    3,247

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

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

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,760

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



  6. #6
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2010
    Posts
    3,247

    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. #7
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2010
    Posts
    3,247

    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. #8
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2010
    Posts
    3,247

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

    While store 25 is the distribution store

  9. #9
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2010
    Posts
    3,247

    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. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,760

    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: copy to clipboard
    Please Login or Register  to view this content.


    I didn't follow your comment about store 25...
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2010
    Posts
    3,247

    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. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,760

    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. #13
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2010
    Posts
    3,247

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

    Okay noted , Maybe I will trick it with less data

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Stock Movement for too many stores ?
    By makinmomb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-28-2015, 08:37 AM
  2. Exponential Growth in Stores
    By mrr2 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-04-2013, 01:21 PM
  3. Replies: 4
    Last Post: 01-29-2013, 02:26 AM
  4. find top 50% of stores
    By Xia in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-10-2012, 06:58 AM
  5. Replies: 0
    Last Post: 10-28-2012, 07:22 AM
  6. Grouping Stores by zip code
    By Vazz in forum Excel General
    Replies: 1
    Last Post: 02-16-2011, 05:05 AM
  7. New tab stores data in an overview...
    By vi54 in forum Excel General
    Replies: 4
    Last Post: 12-20-2009, 06:41 PM

Bookmarks

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