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

M

Morton Detwyler

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!
 
L

Lars-Åke Aspelin

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!
Click to expand...


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
 
L

Lars-Åke Aspelin

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!
Click to expand...


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
Click to expand...

AC3 should be AC2 of course.
 
R

Ron Rosenfeld

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.
Click to expand...

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(ISNUMBER(rng)
*COLUMN(rng),COUNT(rng)+1-COLUMNS($A:A))-COLUMN(rng)),"")

--ron
 
T

T. Valko

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:$Z2,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,MATCH(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)
 
M

Morton Detwyler

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

Morton Detwyler

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 said:
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:$Z2,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,MATCH(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 said:
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!
Click to expand...
Click to expand...
 
M

Morton Detwyler

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 said:
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!
Click to expand...


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
Click to expand...

AC3 should be AC2 of course.
Click to expand...
 
T

T. Valko

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Morton Detwyler said:
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 said:
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:$Z2,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,MATCH(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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA 7
Return Each Last Non-blank Cell, Value 2 Column Offset, Many GAPS 1
I need a cell to be blank. 4
row number of last non-blank cell 14
Copying data entered in the last cell of a row on the same spreads 2
result should equal a blank field 2
Function Help - Forecast Date past due, adjacent cell is blank. Needto highlight the blank cell. 2
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0

Top