Skipping Blank Or Null Cells In a Lookup Function

[Bill Johnson]

I need to pull certain values in rows of data into another tab in a worksheet
but only if the quantity colum contains a value >0. I am currently using an
if statement but it makes me populate a value for even false criteria.

Does anyone know how I can only pull over cells in a row where my quantity
column value is >0.

Source Tab
Example
Description SKU Quantity Price Cost
Product A 1001 100 10.00 5.00
Product B 1002 0 15.00 10.00
Product C 1003 150 8.00 4.00

Detination Tab - I would like these results
Product A 1001 100 10.00 5.00
Product C 1003 150 8.00 4.00

Destination Tab - What I Have Today
Product A 1001 100 10.00 5.00
- - - - -
Product C 1003 150 8.00 4.00

These rows with no values are not valuable and I want to get rid of them.

Any help appreciated!

 

[Max]

One way ..

Assuming the table below is
in Sheet1, cols A to E,
data from row2 down

Description SKU Quantity Price Cost
Product A 1001 100 10.00 5.00
Product B 1002 0 15.00 10.00
Product C 1003 150 8.00 4.00

Put in G2: =IF(C2="","",IF(C2>0,ROW(),""))

Copy G2 down as many rows as data is expected
in the table, say down to G100?
(can copy down ahead to cater for expected data input)

In Sheet2
-------------
With the same headers in A1:E1, ie:

Description SKU Quantity Price Cost

Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet1!$G:$G,ROW(A1)),Sheet1!$G:$G,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$G:$G,ROW(A1)),Sheet1!$G:$G,0)-1,COLUMN(A1)-1))

Copy A2 across to E2, fill down by
as many rows as per col G in Sheet1,
i.e. down to E100

(Format cols D and E as currency)

The above will extract the desired rows from Sheet1,
viz. for the sample data, you'll get:

Description SKU Quantity Price Cost
Product A 1001 100 10.00 5.00
Product C 1003 150 8.00 4.00

< rest are blanks >

Adapt / extend to suit

 

[Biff]

Hi!

Here's another method that doesn't require a helper column.

Assume SOURCE TAB data range = A1:E100 with headers in row
1.

DESTINATION TAB headers also in row 1, A1:E1.

In DESTINATION TAB cell A2 enter this array formula with
the key combo of CTRL,SHIFT,ENTER:

=INDEX(A$2:A$100,SMALL(IF($C$2:$C$100>0,ROW(INDIRECT
("$A$1:$A$"&COUNTA($A$2:$A100)))),ROW(1:1)))

Copy across to E2 then down until you get #NUM! errors
meaning the data has been exhausted.

Biff

 

[Aladin Akyurek]

Run Advanced Filter with computed criteria from within the destination
sheet.

 

[Max]

In DESTINATION TAB cell A2 enter this array formula with
the key combo of CTRL,SHIFT,ENTER:

=INDEX(A$2:A$100,SMALL(IF($C$2:$C$100>0,ROW(INDIRECT
("$A$1:$A$"&COUNTA($A$2:$A100)))),ROW(1:1)))

... think the source sheetname was missed out
in the formula above <g> ..

If the source sheetname is : SOURCE, then:

=INDEX(SOURCE!A$2:A$100,SMALL(IF(SOURCE!$C$2:$C$100>0,ROW(INDIRECT("$A$1:$A$
"&COUNTA(SOURCE!$A$2:$A100)))),ROW(SOURCE!1:1)))

Or, perhaps better? with an error trap to return
a cleaner looking output in the destination sheet:

=IF(ISERROR(SMALL(IF(SOURCE!$C$2:$C$100>0,ROW(INDIRECT("$A$1:$A$"&COUNTA(SOU
RCE!$A$2:$A100)))),ROW(SOURCE!1:1))),"",INDEX(SOURCE!A$2:A$100,SMALL(IF(SOUR
CE!$C$2:$C$100>0,ROW(INDIRECT("$A$1:$A$"&COUNTA(SOURCE!$A$2:$A100)))),ROW(SO
URCE!1:1))))

 

[Bill Johnson]

All,
Thanks for all the suggestions. I will need to keep working at this as I
have tried all the suggestions listed and cannot make any of the suggestion
return exactly what I'm looking for. I'll keep trying the suggestions listed
as well as a macro. Does anyone know if a macro would be better here?

Thanks again and I'll post if I get any of these to work.
Bill

 

[Max]

:
....

I will need to keep working at this as I
have tried all the suggestions listed
and cannot make any of the suggestion
return exactly what I'm looking for. ..

I've got a sample file with both Biff's and my
suggestion implemented in it which works
exactly the way you posted. If you're interested
just post a "readable" email in response here
and I'll send it via private email.

 

[Bill Johnson]

Max,
Thanks a million for the help. Your saving me tons of time.
Send it to (e-mail address removed)

Best Regards,
Bill

 

[Max]

File on its way over, Bill !

And with some luck,
should arrive before Christmas <g>