How to find the first nonzero cell in a row
 

I have a worksheet that has store names as rows, week numbers as columns, and
inventory quantities as cells. I need a column that has a formula that shows
the first week that has inventory at each store.

Basically, I need to find the the column with the first non-zero cell in
each row, then return the week number listed at the top of that column in the
column header. I need the appropriate weeknumber for each row.

Any ideas would be appreciated!

Thanks

Katie

How to find the first nonzero cell in a row
 

On Mon, 17 Oct 2005 12:26:04 -0700, "Katie"
wrote:

I have a worksheet that has store names as rows, week numbers as columns, and
inventory quantities as cells. I need a column that has a formula that shows
the first week that has inventory at each store.

Basically, I need to find the the column with the first non-zero cell in
each row, then return the week number listed at the top of that column in the
column header. I need the appropriate weeknumber for each row.

Any ideas would be appreciated!

Thanks

Katie

Assumptions:

1. Your week numbers are in row 1
2. Your weeknumbers and data start in Column D

This **array** formula will return the contents of row1 that is in the same
column as the first non-zero contents of the referenced row (row 3 in this
instance):

=INDEX($D$1:$IV$1,,MATCH(TRUE,D3:IV30,0))

You can copy/drag the formula down as needed.

To enter an array formula, after typing or pasting it in, you must hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula if you did it correctly.


--ron

How to find the first nonzero cell in a row
 

Thanks! That worked perfectly.

"Ron Rosenfeld" wrote:

On Mon, 17 Oct 2005 12:26:04 -0700, "Katie"
wrote:

I have a worksheet that has store names as rows, week numbers as columns, and
inventory quantities as cells. I need a column that has a formula that shows
the first week that has inventory at each store.

Basically, I need to find the the column with the first non-zero cell in
each row, then return the week number listed at the top of that column in the
column header. I need the appropriate weeknumber for each row.

Any ideas would be appreciated!

Thanks

Katie

Assumptions:

1. Your week numbers are in row 1
2. Your weeknumbers and data start in Column D

This **array** formula will return the contents of row1 that is in the same
column as the first non-zero contents of the referenced row (row 3 in this
instance):

=INDEX($D$1:$IV$1,,MATCH(TRUE,D3:IV30,0))

You can copy/drag the formula down as needed.

To enter an array formula, after typing or pasting it in, you must hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula if you did it correctly.


--ron

How to find the first nonzero cell in a row
 

On Mon, 17 Oct 2005 13:13:04 -0700, "Katie"
wrote:

Thanks! That worked perfectly.

You're most welcome. Thanks for the feedback.

--ron

How to find the first nonzero cell in a row
 

Hi,

I tried the INDEX & MATCH formulas and it works! But if my row # is
dynamic, i.e. take your example below that if row 3 be replaced with
different row #, how to incorporate the dynamic # in MATCH.

Lmm

"Katie" wrote:

Thanks! That worked perfectly.

"Ron Rosenfeld" wrote:

On Mon, 17 Oct 2005 12:26:04 -0700, "Katie"
wrote:

I have a worksheet that has store names as rows, week numbers as columns, and
inventory quantities as cells. I need a column that has a formula that shows
the first week that has inventory at each store.

Basically, I need to find the the column with the first non-zero cell in
each row, then return the week number listed at the top of that column in the
column header. I need the appropriate weeknumber for each row.

Any ideas would be appreciated!

Thanks

Katie

Assumptions:

1. Your week numbers are in row 1
2. Your weeknumbers and data start in Column D

This **array** formula will return the contents of row1 that is in the same
column as the first non-zero contents of the referenced row (row 3 in this
instance):

=INDEX($D$1:$IV$1,,MATCH(TRUE,D3:IV30,0))

You can copy/drag the formula down as needed.

To enter an array formula, after typing or pasting it in, you must hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula if you did it correctly.


--ron

How to find the first nonzero cell in a row
 

Lmm wrote:
Hi,

I tried the INDEX & MATCH formulas and it works! But if my row # is
dynamic, i.e. take your example below that if row 3 be replaced with
different row #, how to incorporate the dynamic # in MATCH.

Hi Lmm,

[snipped]
=INDEX($D$1:$IV$1,,MATCH(TRUE,D3:IV30,0))

In this case row 3 was just an example. Start with the formula in row 3
and fill down, it will adjust.

How to find the first nonzero cell in a row
 

"smartin" wrote:

Lmm wrote:
Hi,

I tried the INDEX & MATCH formulas and it works! But if my row # is
dynamic, i.e. take your example below that if row 3 be replaced with
different row #, how to incorporate the dynamic # in MATCH.

Hi Lmm,

[snipped]
=INDEX($D$1:$IV$1,,MATCH(TRUE,D3:IV30,0))

In this case row 3 was just an example. Start with the formula in row 3
and fill down, it will adjust.


I didn't express my question clearer. Take this example that each inventory
row represents one SKU commit wk units. By giving one SKU and find out the
first nonzero cell for that SKU row is what I am looking for. I am not doing
serial request for all rows together. In summary, if I find the row # for
one specific SKU in qeustion, how to incorporate the row # into the formula?
Thanks.

Lmm

How to find the first nonzero cell in a row
 

Let's see if this is what you want:

...........A..........B..........C..........D
1....................X..........Y..........Z
2....SKU1......0...........0..........5
3....SKU2......1...........2..........4
4....SKU3......0...........3..........1
5....SKU4......0...........0..........2

Return the column header for the 1st non-zero value (assuming there are
no -ve values) that corresponds to SKUx

A10 = SKU3

This array formula** :

=INDEX(B1:D1,,MATCH(TRUE,INDEX(B2:D5,MATCH(A10,A2: A5,0),0)0,0))

Returns Y

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Lmm" wrote in message
...


"smartin" wrote:

Lmm wrote:
Hi,

I tried the INDEX & MATCH formulas and it works! But if my row # is
dynamic, i.e. take your example below that if row 3 be replaced with
different row #, how to incorporate the dynamic # in MATCH.

Hi Lmm,

[snipped]
=INDEX($D$1:$IV$1,,MATCH(TRUE,D3:IV30,0))

In this case row 3 was just an example. Start with the formula in row 3
and fill down, it will adjust.


I didn't express my question clearer. Take this example that each
inventory
row represents one SKU commit wk units. By giving one SKU and find out
the
first nonzero cell for that SKU row is what I am looking for. I am not
doing
serial request for all rows together. In summary, if I find the row # for
one specific SKU in qeustion, how to incorporate the row # into the
formula?
Thanks.

Lmm

How to find the first nonzero cell in a row
 

You are awesome!!! Truly MVP. Thanks! This is what I want.

"T. Valko" wrote:

Let's see if this is what you want:

...........A..........B..........C..........D
1....................X..........Y..........Z
2....SKU1......0...........0..........5
3....SKU2......1...........2..........4
4....SKU3......0...........3..........1
5....SKU4......0...........0..........2

Return the column header for the 1st non-zero value (assuming there are
no -ve values) that corresponds to SKUx

A10 = SKU3

This array formula** :

=INDEX(B1:D1,,MATCH(TRUE,INDEX(B2:D5,MATCH(A10,A2: A5,0),0)0,0))

Returns Y

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


I didn't express my question clearer. Take this example that each
inventory
row represents one SKU commit wk units. By giving one SKU and find out
the
first nonzero cell for that SKU row is what I am looking for. I am not
doing
serial request for all rows together. In summary, if I find the row # for
one specific SKU in qeustion, how to incorporate the row # into the
formula?
Thanks.

Lmm


Hi Lmm,

[snipped]
=INDEX($D$1:$IV$1,,MATCH(TRUE,D3:IV30,0))

In this case row 3 was just an example. Start with the formula in row 3
and fill down, it will adjust.


"Lmm" wrote in message
...


"smartin" wrote:

Lmm wrote:
Hi,

I tried the INDEX & MATCH formulas and it works! But if my row # is
dynamic, i.e. take your example below that if row 3 be replaced with
different row #, how to incorporate the dynamic # in MATCH.

How to find the first nonzero cell in a row
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Lmm" wrote in message
...
You are awesome!!! Truly MVP. Thanks! This is what I want.

"T. Valko" wrote:

Let's see if this is what you want:

...........A..........B..........C..........D
1....................X..........Y..........Z
2....SKU1......0...........0..........5
3....SKU2......1...........2..........4
4....SKU3......0...........3..........1
5....SKU4......0...........0..........2

Return the column header for the 1st non-zero value (assuming there are
no -ve values) that corresponds to SKUx

A10 = SKU3

This array formula** :

=INDEX(B1:D1,,MATCH(TRUE,INDEX(B2:D5,MATCH(A10,A2: A5,0),0)0,0))

Returns Y

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


I didn't express my question clearer. Take this example that each
inventory
row represents one SKU commit wk units. By giving one SKU and find out
the
first nonzero cell for that SKU row is what I am looking for. I am not
doing
serial request for all rows together. In summary, if I find the row #
for
one specific SKU in qeustion, how to incorporate the row # into the
formula?
Thanks.

Lmm


Hi Lmm,

[snipped]
=INDEX($D$1:$IV$1,,MATCH(TRUE,D3:IV30,0))

In this case row 3 was just an example. Start with the formula in row
3
and fill down, it will adjust.


"Lmm" wrote in message
...


"smartin" wrote:

Lmm wrote:
Hi,

I tried the INDEX & MATCH formulas and it works! But if my row # is
dynamic, i.e. take your example below that if row 3 be replaced with
different row #, how to incorporate the dynamic # in MATCH.