ExcelBanter - Skipping Blank Or Null Cells In a Lookup Function

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Skipping Blank Or Null Cells In a Lookup Function (https://www.excelbanter.com/excel-worksheet-functions/8262-skipping-blank-null-cells-lookup-function.html)

Skipping Blank Or Null Cells In a Lookup Function

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!

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(C20,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)),Shee t1!$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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Bill Johnson" <Bill wrote in message
...
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!

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$1000,ROW(INDIRE CT
("$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

-----Original Message-----
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!

.

"Biff" wrote
....
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$1000,ROW(INDIRE CT
("$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$10 00,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$1000,ROW(INDI RECT("$A$1:$A$"&COUNTA(SOU
RCE!$A$2:$A100)))),ROW(SOURCE!1:1))),"",INDEX(SOUR CE!A$2:A$100,SMALL(IF(SOUR
CE!$C$2:$C$1000,ROW(INDIRECT("$A$1:$A$"&COUNTA(SO URCE!$A$2:$A100)))),ROW(SO
URCE!1:1))))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Bill Johnson" wrote:
....
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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

File on its way over, Bill !

And with some luck,
should arrive before Christmas <g

--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----