FINDING THE FIRST ENTERED NUMBER IN A ROW OF DATA AFTER A BLANK CE
 

I pull product metric data into an Excel spreadsheet from an online OLAP Cube
that is returned in a series of rows. Product names are in column [A], and
number headings are in Row [1] (i.e. 1,2,3,4.....). We do not use month
names. Some newly introduced products might not have any appreciable metric
data for the first few months of their existence, so my query returns a blank
into the first few cells of a row. When enough data has been gathered,
metrics will begin to appear. So it's typical that some rows will have
blanks in the first few cells, and then subsequent cells are populated; e.g.
cell A2 = Product Name, cell B2 = blank, cell C2 = blank, cell D2 = 12.57%,
etc. As each product is different, so is the number of blank cells preceding
a metric - some products may have no blank cells, others may have many.

To the right of these metrics, beginning in cell AA2 and going down the
column, I perform a calculation on the first entered metric for each product.
I then must perform another calculation on the second, third, etc. metrics.
For example, AA2 = calc on first entered metric; AA3 = calc on second entered
metric; AA4 = calc on third entered metric, etc.

I'd like my formula in this far right column (AA2) to be able to look to the
left and find the "first" entered metric that proceeds the blank cells. Then
I'd like a formula in cell AA3 to look to the left and find the "second"
entered metric that proceeds the blank cells, a formula in cell AA4 to look
to the left and find the "third" entered metric that proceeds the blank
cells, etc.

I simply can not figure out how to do this. Any help would be greatly
appreciated and make the management of this spreadsheet so much easier. Thank
you very much for any assistance!

FINDING THE FIRST ENTERED NUMBER IN A ROW OF DATA AFTER A BLANK CE
 

On Sat, 19 Jul 2008 14:27:00 -0700, Morton Detwyler
wrote:

I pull product metric data into an Excel spreadsheet from an online OLAP Cube
that is returned in a series of rows. Product names are in column [A], and
number headings are in Row [1] (i.e. 1,2,3,4.....). We do not use month
names. Some newly introduced products might not have any appreciable metric
data for the first few months of their existence, so my query returns a blank
into the first few cells of a row. When enough data has been gathered,
metrics will begin to appear. So it's typical that some rows will have
blanks in the first few cells, and then subsequent cells are populated; e.g.
cell A2 = Product Name, cell B2 = blank, cell C2 = blank, cell D2 = 12.57%,
etc. As each product is different, so is the number of blank cells preceding
a metric - some products may have no blank cells, others may have many.

To the right of these metrics, beginning in cell AA2 and going down the
column, I perform a calculation on the first entered metric for each product.
I then must perform another calculation on the second, third, etc. metrics.
For example, AA2 = calc on first entered metric; AA3 = calc on second entered
metric; AA4 = calc on third entered metric, etc.

I'd like my formula in this far right column (AA2) to be able to look to the
left and find the "first" entered metric that proceeds the blank cells. Then
I'd like a formula in cell AA3 to look to the left and find the "second"
entered metric that proceeds the blank cells, a formula in cell AA4 to look
to the left and find the "third" entered metric that proceeds the blank
cells, etc.

I simply can not figure out how to do this. Any help would be greatly
appreciated and make the management of this spreadsheet so much easier. Thank
you very much for any assistance!


Do you really mean AA2, AA3, AA4 etc?
It would be more natural to use AA2, AB2, AC3 etc if these are data
for one product, the product on row 2.

Assuming there are no "gaps" in the data, just blanks in the
beginning, you can try this formula in cell AA2:

=INDEX($B2:$Z2,COLUMN()+72-MAX(ISNUMBER($B2:$Z2)*(100-COLUMN($B2:$Z2))))

This is an array formula and must be confirmed with CTRL+SHIFT+ENTER
rather than just ENTER.

Copy the formula to the right as far as needed to have the second,
third, etc values in columns AB, AC.
Copy down as far as needed to have the similar values for the other
products (on row 3, 4, etc)

Hope this helps / Lars-Åke

FINDING THE FIRST ENTERED NUMBER IN A ROW OF DATA AFTER A BLANK CE
 

On Sat, 19 Jul 2008 22:59:55 GMT, Lars-Åke Aspelin
wrote:

On Sat, 19 Jul 2008 14:27:00 -0700, Morton Detwyler
wrote:

I pull product metric data into an Excel spreadsheet from an online OLAP Cube
that is returned in a series of rows. Product names are in column [A], and
number headings are in Row [1] (i.e. 1,2,3,4.....). We do not use month
names. Some newly introduced products might not have any appreciable metric
data for the first few months of their existence, so my query returns a blank
into the first few cells of a row. When enough data has been gathered,
metrics will begin to appear. So it's typical that some rows will have
blanks in the first few cells, and then subsequent cells are populated; e.g.
cell A2 = Product Name, cell B2 = blank, cell C2 = blank, cell D2 = 12.57%,
etc. As each product is different, so is the number of blank cells preceding
a metric - some products may have no blank cells, others may have many.

To the right of these metrics, beginning in cell AA2 and going down the
column, I perform a calculation on the first entered metric for each product.
I then must perform another calculation on the second, third, etc. metrics.
For example, AA2 = calc on first entered metric; AA3 = calc on second entered
metric; AA4 = calc on third entered metric, etc.

I'd like my formula in this far right column (AA2) to be able to look to the
left and find the "first" entered metric that proceeds the blank cells. Then
I'd like a formula in cell AA3 to look to the left and find the "second"
entered metric that proceeds the blank cells, a formula in cell AA4 to look
to the left and find the "third" entered metric that proceeds the blank
cells, etc.

I simply can not figure out how to do this. Any help would be greatly
appreciated and make the management of this spreadsheet so much easier. Thank
you very much for any assistance!


Do you really mean AA2, AA3, AA4 etc?
It would be more natural to use AA2, AB2, AC3 etc if these are data
for one product, the product on row 2.

Assuming there are no "gaps" in the data, just blanks in the
beginning, you can try this formula in cell AA2:

=INDEX($B2:$Z2,COLUMN()+72-MAX(ISNUMBER($B2:$Z2)*(100-COLUMN($B2:$Z2))))

This is an array formula and must be confirmed with CTRL+SHIFT+ENTER
rather than just ENTER.

Copy the formula to the right as far as needed to have the second,
third, etc values in columns AB, AC.
Copy down as far as needed to have the similar values for the other
products (on row 3, 4, etc)

Hope this helps / Lars-Åke

AC3 should be AC2 of course.

FINDING THE FIRST ENTERED NUMBER IN A ROW OF DATA AFTER A BLANK CE
 

On Sat, 19 Jul 2008 14:27:00 -0700, Morton Detwyler
wrote:

I'd like my formula in this far right column (AA2) to be able to look to the
left and find the "first" entered metric that proceeds the blank cells. Then
I'd like a formula in cell AA3 to look to the left and find the "second"
entered metric that proceeds the blank cells, a formula in cell AA4 to look
to the left and find the "third" entered metric that proceeds the blank
cells, etc.

Do you really want the formulas in AA2 and AA3? Or did you perhaps mean AB2
and AC2? If the latter:

Enter this **array** formula in AA2; then fill right as far as required.

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula if you did it correctly.

In the formula below, "rng" is a named range. If it refers to: $B2:$Z2, then
as you fill down, the row reference will change appropriately .

AA2:

=IF(COUNT(rng)=COLUMNS($A:A),OFFSET(rng,0,LARGE(I SNUMBER(rng)
*COLUMN(rng),COUNT(rng)+1-COLUMNS($A:A))-COLUMN(rng)),"")

--ron

FINDING THE FIRST ENTERED NUMBER IN A ROW OF DATA AFTER A BLANK CE
 

There seems to be some question as to whether you want the data extracted
across a row horizontally or down a column vertically. So, here's both. Both
formulas are array formulas**.

Across the row horizontally...

=IF(COLUMNS($AA2:AA2)<=COUNT($B2:$Z2),INDEX($B2:$Z 2,MATCH(TRUE,ISNUMBER($B2:$Z2),0)+COLUMNS($AA2:AA2 )-1),"")

Down the column vertically...

=IF(ROWS(AA$2:AA2)<=COUNT(B$2:Z$2),INDEX(B$2:Z$2,M ATCH(TRUE,ISNUMBER(B$2:Z$2),0)+ROWS(AA$2:AA2)-1),"")

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

--
Biff
Microsoft Excel MVP


"Morton Detwyler" wrote in
message ...
I pull product metric data into an Excel spreadsheet from an online OLAP
Cube
that is returned in a series of rows. Product names are in column [A], and
number headings are in Row [1] (i.e. 1,2,3,4.....). We do not use month
names. Some newly introduced products might not have any appreciable
metric
data for the first few months of their existence, so my query returns a
blank
into the first few cells of a row. When enough data has been gathered,
metrics will begin to appear. So it's typical that some rows will have
blanks in the first few cells, and then subsequent cells are populated;
e.g.
cell A2 = Product Name, cell B2 = blank, cell C2 = blank, cell D2 =
12.57%,
etc. As each product is different, so is the number of blank cells
preceding
a metric - some products may have no blank cells, others may have many.

To the right of these metrics, beginning in cell AA2 and going down the
column, I perform a calculation on the first entered metric for each
product.
I then must perform another calculation on the second, third, etc.
metrics.
For example, AA2 = calc on first entered metric; AA3 = calc on second
entered
metric; AA4 = calc on third entered metric, etc.

I'd like my formula in this far right column (AA2) to be able to look to
the
left and find the "first" entered metric that proceeds the blank cells.
Then
I'd like a formula in cell AA3 to look to the left and find the "second"
entered metric that proceeds the blank cells, a formula in cell AA4 to
look
to the left and find the "third" entered metric that proceeds the blank
cells, etc.

I simply can not figure out how to do this. Any help would be greatly
appreciated and make the management of this spreadsheet so much easier.
Thank
you very much for any assistance!

FINDING THE FIRST ENTERED NUMBER IN A ROW OF DATA AFTER A BLAN
 

Ron,
I can't thank you enough for your assistance. Your formula worked perfectly!

"Ron Rosenfeld" wrote:

On Sat, 19 Jul 2008 14:27:00 -0700, Morton Detwyler
wrote:

I'd like my formula in this far right column (AA2) to be able to look to the
left and find the "first" entered metric that proceeds the blank cells. Then
I'd like a formula in cell AA3 to look to the left and find the "second"
entered metric that proceeds the blank cells, a formula in cell AA4 to look
to the left and find the "third" entered metric that proceeds the blank
cells, etc.

Do you really want the formulas in AA2 and AA3? Or did you perhaps mean AB2
and AC2? If the latter:

Enter this **array** formula in AA2; then fill right as far as required.

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula if you did it correctly.

In the formula below, "rng" is a named range. If it refers to: $B2:$Z2, then
as you fill down, the row reference will change appropriately .

AA2:

=IF(COUNT(rng)=COLUMNS($A:A),OFFSET(rng,0,LARGE(I SNUMBER(rng)
*COLUMN(rng),COUNT(rng)+1-COLUMNS($A:A))-COLUMN(rng)),"")

--ron

FINDING THE FIRST ENTERED NUMBER IN A ROW OF DATA AFTER A BLAN
 

Biff,
Thank you so much for providing both formulas. They worked perfectly and
both are applicable to my situation. Your assistance was invaluable!

"T. Valko" wrote:

There seems to be some question as to whether you want the data extracted
across a row horizontally or down a column vertically. So, here's both. Both
formulas are array formulas**.

Across the row horizontally...

=IF(COLUMNS($AA2:AA2)<=COUNT($B2:$Z2),INDEX($B2:$Z 2,MATCH(TRUE,ISNUMBER($B2:$Z2),0)+COLUMNS($AA2:AA2 )-1),"")

Down the column vertically...

=IF(ROWS(AA$2:AA2)<=COUNT(B$2:Z$2),INDEX(B$2:Z$2,M ATCH(TRUE,ISNUMBER(B$2:Z$2),0)+ROWS(AA$2:AA2)-1),"")

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

--
Biff
Microsoft Excel MVP


"Morton Detwyler" wrote in
message ...
I pull product metric data into an Excel spreadsheet from an online OLAP
Cube
that is returned in a series of rows. Product names are in column [A], and
number headings are in Row [1] (i.e. 1,2,3,4.....). We do not use month
names. Some newly introduced products might not have any appreciable
metric
data for the first few months of their existence, so my query returns a
blank
into the first few cells of a row. When enough data has been gathered,
metrics will begin to appear. So it's typical that some rows will have
blanks in the first few cells, and then subsequent cells are populated;
e.g.
cell A2 = Product Name, cell B2 = blank, cell C2 = blank, cell D2 =
12.57%,
etc. As each product is different, so is the number of blank cells
preceding
a metric - some products may have no blank cells, others may have many.

To the right of these metrics, beginning in cell AA2 and going down the
column, I perform a calculation on the first entered metric for each
product.
I then must perform another calculation on the second, third, etc.
metrics.
For example, AA2 = calc on first entered metric; AA3 = calc on second
entered
metric; AA4 = calc on third entered metric, etc.

I'd like my formula in this far right column (AA2) to be able to look to
the
left and find the "first" entered metric that proceeds the blank cells.
Then
I'd like a formula in cell AA3 to look to the left and find the "second"
entered metric that proceeds the blank cells, a formula in cell AA4 to
look
to the left and find the "third" entered metric that proceeds the blank
cells, etc.

I simply can not figure out how to do this. Any help would be greatly
appreciated and make the management of this spreadsheet so much easier.
Thank
you very much for any assistance!

FINDING THE FIRST ENTERED NUMBER IN A ROW OF DATA AFTER A BLAN
 

Lars,
Thank you for your assistance. Unfortunately, I keep getting a #VALUE!
error when using your formula, and can't seem to clear it. But nonetheless,
your response is appreciated.

"Lars-Ã…ke Aspelin" wrote:

On Sat, 19 Jul 2008 22:59:55 GMT, Lars-Ã…ke Aspelin
wrote:

On Sat, 19 Jul 2008 14:27:00 -0700, Morton Detwyler
wrote:

I pull product metric data into an Excel spreadsheet from an online OLAP Cube
that is returned in a series of rows. Product names are in column [A], and
number headings are in Row [1] (i.e. 1,2,3,4.....). We do not use month
names. Some newly introduced products might not have any appreciable metric
data for the first few months of their existence, so my query returns a blank
into the first few cells of a row. When enough data has been gathered,
metrics will begin to appear. So it's typical that some rows will have
blanks in the first few cells, and then subsequent cells are populated; e.g.
cell A2 = Product Name, cell B2 = blank, cell C2 = blank, cell D2 = 12.57%,
etc. As each product is different, so is the number of blank cells preceding
a metric - some products may have no blank cells, others may have many.

To the right of these metrics, beginning in cell AA2 and going down the
column, I perform a calculation on the first entered metric for each product.
I then must perform another calculation on the second, third, etc. metrics.
For example, AA2 = calc on first entered metric; AA3 = calc on second entered
metric; AA4 = calc on third entered metric, etc.

I'd like my formula in this far right column (AA2) to be able to look to the
left and find the "first" entered metric that proceeds the blank cells. Then
I'd like a formula in cell AA3 to look to the left and find the "second"
entered metric that proceeds the blank cells, a formula in cell AA4 to look
to the left and find the "third" entered metric that proceeds the blank
cells, etc.

I simply can not figure out how to do this. Any help would be greatly
appreciated and make the management of this spreadsheet so much easier. Thank
you very much for any assistance!


Do you really mean AA2, AA3, AA4 etc?
It would be more natural to use AA2, AB2, AC3 etc if these are data
for one product, the product on row 2.

Assuming there are no "gaps" in the data, just blanks in the
beginning, you can try this formula in cell AA2:

=INDEX($B2:$Z2,COLUMN()+72-MAX(ISNUMBER($B2:$Z2)*(100-COLUMN($B2:$Z2))))

This is an array formula and must be confirmed with CTRL+SHIFT+ENTER
rather than just ENTER.

Copy the formula to the right as far as needed to have the second,
third, etc values in columns AB, AC.
Copy down as far as needed to have the similar values for the other
products (on row 3, 4, etc)

Hope this helps / Lars-Ã…ke

AC3 should be AC2 of course.

FINDING THE FIRST ENTERED NUMBER IN A ROW OF DATA AFTER A BLAN
 

On Sun, 20 Jul 2008 07:30:01 -0700, Morton Detwyler
wrote:

Ron,
I can't thank you enough for your assistance. Your formula worked perfectly!

Glad to help. Thanks for the feedback.
--ron

FINDING THE FIRST ENTERED NUMBER IN A ROW OF DATA AFTER A BLAN
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Morton Detwyler" wrote in
message ...
Biff,
Thank you so much for providing both formulas. They worked perfectly and
both are applicable to my situation. Your assistance was invaluable!

"T. Valko" wrote:

There seems to be some question as to whether you want the data extracted
across a row horizontally or down a column vertically. So, here's both.
Both
formulas are array formulas**.

Across the row horizontally...

=IF(COLUMNS($AA2:AA2)<=COUNT($B2:$Z2),INDEX($B2:$Z 2,MATCH(TRUE,ISNUMBER($B2:$Z2),0)+COLUMNS($AA2:AA2 )-1),"")

Down the column vertically...

=IF(ROWS(AA$2:AA2)<=COUNT(B$2:Z$2),INDEX(B$2:Z$2,M ATCH(TRUE,ISNUMBER(B$2:Z$2),0)+ROWS(AA$2:AA2)-1),"")

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

--
Biff
Microsoft Excel MVP


"Morton Detwyler" wrote in
message ...
I pull product metric data into an Excel spreadsheet from an online OLAP
Cube
that is returned in a series of rows. Product names are in column [A],
and
number headings are in Row [1] (i.e. 1,2,3,4.....). We do not use month
names. Some newly introduced products might not have any appreciable
metric
data for the first few months of their existence, so my query returns a
blank
into the first few cells of a row. When enough data has been gathered,
metrics will begin to appear. So it's typical that some rows will have
blanks in the first few cells, and then subsequent cells are populated;
e.g.
cell A2 = Product Name, cell B2 = blank, cell C2 = blank, cell D2 =
12.57%,
etc. As each product is different, so is the number of blank cells
preceding
a metric - some products may have no blank cells, others may have many.

To the right of these metrics, beginning in cell AA2 and going down the
column, I perform a calculation on the first entered metric for each
product.
I then must perform another calculation on the second, third, etc.
metrics.
For example, AA2 = calc on first entered metric; AA3 = calc on second
entered
metric; AA4 = calc on third entered metric, etc.

I'd like my formula in this far right column (AA2) to be able to look
to
the
left and find the "first" entered metric that proceeds the blank cells.
Then
I'd like a formula in cell AA3 to look to the left and find the
"second"
entered metric that proceeds the blank cells, a formula in cell AA4 to
look
to the left and find the "third" entered metric that proceeds the blank
cells, etc.

I simply can not figure out how to do this. Any help would be greatly
appreciated and make the management of this spreadsheet so much easier.
Thank
you very much for any assistance!