IF excel 2003

Microsoft Excel 2003 (full product)
August 12, 2010 at 09:04:32
Specs: Windows 2003
I've got 50 rows of product descriptions in Column A. Quantity of product in Column B. I want to create a list on a different workbook within the same document listing only the products that have quantities next to them, but I just can't work out how to do it.. Please help!
Thanks, Janet

See More: IF excel 2003

Report •

August 12, 2010 at 09:32:30
re: "I want to create a list on a different workbook within the same document."

Do you mean a different worksheet within the same document?

Workbook and Document are usually considered to be the same thing.

Report •

August 12, 2010 at 09:38:27
Thanks for your reply DerbyDad03.

Apologies for the wrong termination, yes I meant worksheet within the same document.



Report •

August 26, 2010 at 10:46:27

With your input data in Sheet1 and your desired output in Sheet2, try this:

1 - In A1, enter this formula: =IF(Sheet1!$B1>0,Sheet1!A1,"")
2 - Drag it over to B1, then drag both cells down as far as you need.
3 - Edit...Copy...Edit...PasteSpecial...Values
4 - Sort
5 - Delete blank rows


Option Explicit
Sub ProductQuantity()
Dim lastSrc_row, nxtDst_row, srcData As Integer
'Find last cell with data in Sheet1 Column A
 lastSrc_row = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through Sheet1 data, copying Sheet1 Columns A & B to
'the Next empty row in Sheet2 if Sheet1 Column B is > 0
   For srcData = 2 To lastSrc_row
     If Sheets(1).Range("A" & srcData).Offset(0, 1) > 0 Then
      nxtDst_row = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
      Sheets(1).Range("A" & srcData & ":B" & srcData).Copy _
        Destination:=Sheets(2).Range("A" & nxtDst_row)
     End If
End Sub

Report •

Related Solutions

Ask Question