+ Reply to Thread
Results 1 to 3 of 3

Macro to create and sort inventory data

  1. #1
    Registered User
    Join Date
    10-17-2009
    Location
    Long Island, New York
    MS-Off Ver
    Excel 2003
    Posts
    5

    Exclamation Macro to create and sort inventory data

    Hello, it's hard to explain my situation but here I go. I work for a warehouse, and we use 2 set's of spreadsheets. We use our spreadsheet that contains a catalog of the items that we are authorized to carry, on our website. My catalog I use now, contains 2 columns of data, 1 : the model number, and 2: the YES/NO data if that item is in stock or not... Now your wondering where are we getting the actual number inventory from, we now use a 2nd spreadsheet given to us by the warehouse, that contains the list of model numbers and the number of inventory left in stock, numbers range from 0 to any other number. I want to be able to create a function or macro, that can match the rows of the model numbers and the YES/NO towards the rows in the model number and if the inventory = > 0. So basically, I want the function to be...comparing both the worksheets, and matching the model numbers and looking at the inventory number, and if it's over 0, it'll create a 3rd worksheet with all the data, and make the YES/NO entries for me. I hope I was able to explain it correctly, If needed, I will post a picture of what I'm hoping to achieve in this situation, Any feedback would be GREATLY appreciated, I hope someone knows how to do this.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Macro to create and sort inventory data

    You can do this with formulas, attach dummy workbooks
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Valued Forum Contributor
    Join Date
    06-17-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003,excel 2007
    Posts
    678

    Re: Macro to create and sort inventory data

    I am rephrasing your problem
    sheet2 is your main sheet containing in col A and B the model number and no. available ( 0 to any value).
    if the number available is 0 you want in sheet against the particular model no. you want the entry "no" is made in col. B of that sheet
    all model numbers which are available (that is "yes" in sheet 1 should be copied in sheet 3.

    I wonder what is the need of sheet1.

    Keep your original database safe.

    now run this macro "test" in which is embedded the other macro test1.
    you can modify the macro(s).

    Please Login or Register  to view this content.
    I am attaching my sample file "royUK" so that you can understand the logic of the macros and modify them to suit you.The sheets are AFTER running the macro "test". you can again rerun the macro "test" only.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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