+ Reply to Thread
Results 1 to 7 of 7

Inventory Related Help

  1. #1
    Registered User
    Join Date
    08-24-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    6

    Question Inventory Related Help

    Hello,
    I am not entirely sure what im looking for to accomplish what i want to do, hence why im here. my problem is i have an ever changing inventory system it is 3 columns infinitely long. (column A,B,C) Column A is for Dates Column B is the name of the produce (many reoccurring names) and column C is for the SKU's of the product. what i want to be able to do is have a drop down list i can chose a name from and it will display all the SKU's that pertain to that specific item. so i have something like this:
    A | B | C
    8/1/1 |Item A |2090100
    8/1/1 |Item C |2180101
    8/1/1 |Item A |6090101

    i want a drop down menu i can chose from a range of item names and it puts the corresponding SKU's in a list, the first number of the SKU is irrelevant, the second,third and fourth numbers are what the item is(EX, Item A 090, Item C 180) but the list shows up as shown above. the dates don't matter for this list just the SKU pertaining to the selected item.

    thanks.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,311

    Re: Inventory Related Help

    Something like this?
    A
    B
    C
    D
    E
    F
    G
    1
    Date name SKU
    123
    Date name
    2
    1-Jan
    aa
    123
    1-Jan
    aa
    3
    2-Jan
    bb
    234
    3-Jan
    cc
    4
    3-Jan
    cc
    123
    5-Jan
    bb
    5
    4-Jan
    aa
    345
    7-Jan
    aa
    6
    5-Jan
    bb
    123
    7
    6-Jan
    cc
    234
    8
    7-Jan
    aa
    123
    9
    8-Jan
    bb
    345
    10
    9-Jan
    cc
    456

    E1= the SKU you want
    F2=IFERROR(INDEX(A:A,SMALL(IF($C$2:$C$10=$E$1,ROW($A$2:$A$10)),ROWS($A$1:A1))),"")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Then copy down and across as needed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    08-24-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Inventory Related Help

    similar to what im looking for but not quite. take E1 make it a drop down to select a name then G column would be a list of the 7 digit SKU's that match the item. I have SKU's like this
    EX.
    0180106 <- for the skus the first number is not important to the search but still needs to be present, the 180 is the item that would be selected in the drop down list
    1180000
    3180005
    5090111 <- 090
    6090100
    0260007 <- 260
    4260002

    so when I chose "item A" from the drop down list every 7 digit SKU pertaining to "item A" should appear in a list taken from the master list of jumbled around SKUs.

    this is kind of what im looking for
    EX.

    cell E1...Column G
    Item A...0180106
    ............1180000
    ............3180005...ect

    sorry for the late reply (work) and thank you for posting. sorry im not very good with forums any more.
    Last edited by vennum6; 08-19-2016 at 05:01 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Inventory Related Help

    Hi,

    This may be possible with a Pivot Table but would need a copy of your de-sensitised raw data in order to test.

    Regards

    peterrc

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,746

    Re: Inventory Related Help

    This solution uses three helper columns (K:M) which are hidden. The first two helper columns contain a list of the items and SKU's. The third helper column is populated using the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Column I is then populated using a filtering array entered formula for versions of excel previous to 2010:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    *Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let me know if you have any questions.
    Attached Files Attached Files
    Last edited by JeteMc; 08-20-2016 at 09:50 AM. Reason: Added .xls file
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,311

    Re: Inventory Related Help

    Quote Originally Posted by vennum6 View Post
    similar to what im looking for but not quite. take E1 make it a drop down to select a name then G column would be a list of the 7 digit SKU's that match the item. I have SKU's like this...
    I suggest then that you

    upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

  7. #7
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Inventory Related Help

    So are your items like this? Or do you have a specific 3 digit number for item A and you want that to be in the drop down table instead.



    Date Item SKU Helper
    Date 180 0180106 =B2&"_"&countif($C$2:$C2,"*"&B2&"*")
    Date 180 1180000
    Date 180 3180005
    Date 090 5090111
    Date 090 6090100
    Date 260 0260007
    Date 260 4260002


    Either way, you need to list all of the 3 digit numbers in a separate row, so you can use data validation to make your drop down table. Once you have your list, we can proceed to the next step with a small sample size. Do you know how many matches your maximum amount of matches will be? Because you'd want the matching formula to be set up in the "max" amount of cells for your output. Do you want your list to go down or across? Are you opposed to having it more like:

    Sheet 2
    Criteria 1st Match 2nd Match 3rd Match 4th Match
    180 =IFERROR(INDEX(Sheet1!$C$2:$C$8,MATCH(Sheet2!$A2&"_"&COLUMNS(Sheet2!$B:B),$D$2:$D$8,0)),"")
    090
    260
    720

    or

    Sheet 2
    Criteria 180 090 260 720
    1st Match =IFERROR(INDEX(Sheet1!$C$2:$C$8,MATCH(Sheet2!B$1&"_"&ROWS(Sheet2!$B$2:$B2),$D$2:$D$8,0)),"")
    2nd Match
    3rd Match
    4th Match

    Because then you can see all of your data without having to go through a gigantic drop down menu depending on how large your data size is. A possibility of 1000 unique values to match against isn't something I'd want to scroll through. I await your answers, and hopefully a sample sheet.

    Hopefully my formulas are right, I just typed them out since no sample book to work with, too lazy to open excel. Logic provided by Pete_UK and adapted to this (he helped me with a similar problem).
    Last edited by TheN; 08-21-2016 at 01:47 AM.

+ 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. I want to extend related tables that have related formulas
    By earlyyyyyy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-10-2015, 10:08 AM
  2. [SOLVED] Need Help with Inventory Sheet: Adding to the inventory and Logging
    By petg in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-09-2015, 02:26 PM
  3. Want to set Week End Inventory #s to load in Opening Inventory
    By tkeat1692 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-05-2013, 08:58 PM
  4. [SOLVED] Need remaining average inventory price after partial inventory is sold
    By Akano in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-04-2012, 04:12 AM
  5. Inventory Age Formula Needed for FIFO Inventory
    By SWeisser in forum Excel General
    Replies: 0
    Last Post: 07-20-2012, 02:44 PM
  6. Inventory Stock Balancing with Inventory Data
    By bingbangboom in forum Excel General
    Replies: 2
    Last Post: 01-30-2011, 11:06 PM
  7. FIFO Inventory- COGS & Inventory Valuation
    By aromaveda in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-24-2009, 02:05 AM

Tags for this Thread

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