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

1. ## 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.

2. ## 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)), "")

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

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

4. ## 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.

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

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