+ Reply to Thread
Results 1 to 4 of 4

New worksheet listing only listed items with quantity from a master list

  1. #1
    Registered User
    Join Date
    06-26-2012
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    2

    New worksheet listing only listed items with quantity from a master list

    Solarguy here. In Excel 2010 [not 2003] I have created a very long master list of inventory items over the years in Excel. Columns list name, quantity, marked up cost, my cost, part number and price. This worksheet totals all prices, figures in sales tax, labor, travel fee and other items and moves those totals to another worksheet in a printable format.
    Now I want to take from the master list only items from those rows that display a number in the "Qty" column and move most of the data to yet another wworksheet to create a purchase order. I only want the specific column item from rows with a quantity entered and not, for instance, my cost.
    I suspect that the formula will include "concatenate" and "IF" but playing around with the formulas has not worked even close.
    Any help provided would be appreciated.
    Last edited by solarguy; 06-26-2012 at 09:07 PM. Reason: SHOULD READ V 2010

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: New worksheet listing only listed items with quantity from a master list

    On your inventory sheet I would add a new INDEX column. Let's say it's column Z. If the QTY column is column B, then I would put this formula in Z2 and copy down the whole dataset:

    =IF($B2=0, N(Z1), N(Z1)+1)

    Now you have a sequential index of all rows that have a QTY value, the other rows are just duplicates of already used index values.

    Now on your sheet2 you can use that index to draw over the information from the first row with a "1" in it... let's say you wanted the Name and QTY columns. On sheet2 in cell A3 (I assume you'll need some rows at the top for "stuff"), in A3 or A10 or whatever, enter this first formula:

    =INDEX(Sheet1!A:A, MATCH(ROW($A1), Sheet1!$Z:$Z, 0))

    Copy that cell to B3 and it adjusts itself to:

    =INDEX(Sheet1!B:B, MATCH(ROW($A1), Sheet1!$Z:$Z, 0))

    ...this causing the QTY to appear.

    Continue copying to the right and stop and edit the column in red if you want to skip over something.

    Once you have all the "first row" values appearing properly and formatted, copy that row downwards and the next values will appear as the ROW($A1) adjusts to A2, and A3 (incrementing the index values collected for each row.

    At some point you will get errors, you've reached the end.

    If you want to be able to copy those formulas down even further so the Sheet2 list expands itself as you add QTY in Sheet1, change the initial formula to this:

    =IFERROR(INDEX(Sheet1!A:A, MATCH(ROW($A1), Sheet1!$Z:$Z, 0)), "")
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    06-26-2012
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: New worksheet listing only listed items with quantity from a master list

    Works as advertised JBeaucaire.
    But what's not happening is the Qty, P/N, Name etc data from random rows on DESIGN, out of hundreds of rows, is not appearing in the next line on SHEET1.
    On the screenshots attached the code finds all the data and populates SHEET1 row B correctly. What's not happening is row C should search out and display the next series of data, P/N, Item, Qty etc, on the following rows.
    Or did I just miss something....
    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: New worksheet listing only listed items with quantity from a master list

    From your picture it looks like you skipped rows for that column N formula, you can't do that. Put the formula at the top of column N, in N2, then copy straight down, skipping no rows.

    Trust me.

+ 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