Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 16th 05, 03:45 AM
Sam via OfficeKB.com
 
Posts: n/a
Default Return Count for LAST NonBlank Cell in each Row

Hi All,

I would like to find the LAST NonBlank TEXT Cell in a Row and subtract the
Column number of the Cell from the total number of Columns in the Row.

Or, if feasible to Count back from the LAST Column in my data Range -
Column "L" and SUM the Count of Blank Cells to the very FIRST NonBlank Cell
in each Row. Counting from Right to Left? The Expected Results below is the
value required for each Row.

Or, a more suitable calculation/Formula to provide the Expected Results
below.

The Row consists of 10 Columns - from "C" to "L", first Row is Row 5. The
individual Cells either house an "x" or is blank (empty).

My data Range:
1st Column = "C"
2nd Column = "D"
3rd Column = "E"
4th Column = "F"
5th Column = "G"
6th Column = "H"
7th Column = "I"
8th Column = "J"
9th Column = "K"
10th Column= "L" (LAST Column)

Data:
Row5 "x" in Column C, G, H
Row6 "x" in Column K, L
Row7 "x" in Column K
Row8 "x" in Column G
Row9 "x" in Column L
Row10"x" in Column C
Row11"x" in Column C, G, I
Row12"x" in Column - (no x's in this Row)

Calculation Required:
Find last NonBlank Cell in each Row, then subtract that cells Column Number
from Last Column Number in data Range.

Row5 = Column L (10) minus Column H (6) = 4
Row6 = Column L (10) minus Column L (10)= 0
Row7 = Column L (10) minus Column K (9) = 1
Row12= Column L (10) minus Column ? = 10 (no x's in this Row)

Expected Results (Returned in Column "O" relevant Row Number):
Row5 = 4
Row6 = 0
Row7 = 1
Row8 = 5
Row9 = 0
Row10= 9
Row11= 3
Row12=10

Assistance very much appreciated.

Regards,
Sam

--
Message posted via http://www.officekb.com

  #2   Report Post  
Old April 16th 05, 04:01 AM
Domenic
 
Posts: n/a
Default

Try...

O5, copied down:

=10-IF(COUNTIF(C5:L5,"x"),LOOKUP(REPT("z",255),C5:L5,C OLUMN(C5:L5)-COLUMN
(C5)+1))

Hope this helps!

In article ,
"Sam via OfficeKB.com" wrote:

Hi All,

I would like to find the LAST NonBlank TEXT Cell in a Row and subtract the
Column number of the Cell from the total number of Columns in the Row.

Or, if feasible to Count back from the LAST Column in my data Range -
Column "L" and SUM the Count of Blank Cells to the very FIRST NonBlank Cell
in each Row. Counting from Right to Left? The Expected Results below is the
value required for each Row.

Or, a more suitable calculation/Formula to provide the Expected Results
below.

The Row consists of 10 Columns - from "C" to "L", first Row is Row 5. The
individual Cells either house an "x" or is blank (empty).

My data Range:
1st Column = "C"
2nd Column = "D"
3rd Column = "E"
4th Column = "F"
5th Column = "G"
6th Column = "H"
7th Column = "I"
8th Column = "J"
9th Column = "K"
10th Column= "L" (LAST Column)

Data:
Row5 "x" in Column C, G, H
Row6 "x" in Column K, L
Row7 "x" in Column K
Row8 "x" in Column G
Row9 "x" in Column L
Row10"x" in Column C
Row11"x" in Column C, G, I
Row12"x" in Column - (no x's in this Row)

Calculation Required:
Find last NonBlank Cell in each Row, then subtract that cells Column Number
from Last Column Number in data Range.

Row5 = Column L (10) minus Column H (6) = 4
Row6 = Column L (10) minus Column L (10)= 0
Row7 = Column L (10) minus Column K (9) = 1
Row12= Column L (10) minus Column ? = 10 (no x's in this Row)

Expected Results (Returned in Column "O" relevant Row Number):
Row5 = 4
Row6 = 0
Row7 = 1
Row8 = 5
Row9 = 0
Row10= 9
Row11= 3
Row12=10

Assistance very much appreciated.

Regards,
Sam

  #3   Report Post  
Old April 16th 05, 11:10 PM
Sam via OfficeKB.com
 
Posts: n/a
Default

Hi Domenic,

Thank you for reply. I've tried your suggested Formula below:

=10-IF(COUNTIF(C5:L5,"x"),LOOKUP(REPT("z",255),C5:L5,C OLUMN(C5:L5)-COLUMN
(C5)+1))

The Formula only produces the correct answer for Rows when there a
No x's - returns correct answer of 10 or when an "x" is in last column -
Column L - returns correct answer of 0.

The Formula returns an incorrect 0 (zero) if the LAST "x" in the Row is in
any other column position besides the LAST Column "L".

From the example data below - the Formula should produce 1 (one) in Row 7
but instead it returns an incorrect 0 (zero).

The Row consists of 10 Columns - from "C" to "L", first Row is Row 5. The
individual Cells either house an "x" or is blank (empty).

My data Range:
1st Column = "C"
2nd Column = "D"
3rd Column = "E"
4th Column = "F"
5th Column = "G"
6th Column = "H"
7th Column = "I"
8th Column = "J"
9th Column = "K"
10th Column= "L" (LAST Column)

Data:
Row5 "x" in Column C, G, H
Row6 "x" in Column K, L
Row7 "x" in Column K
Row8 "x" in Column G
Row9 "x" in Column L
Row10"x" in Column C
Row11"x" in Column C, G, I
Row12"x" in Column - (no x's in this Row)

Calculation Required:
Find last NonBlank Cell in each Row, then subtract that cells Column Number
from Last Column Number in data Range.

Row5 = Column L (10) minus Column H (6) = 4
Row6 = Column L (10) minus Column L (10)= 0
Row7 = Column L (10) minus Column K (9) = 1
Row12= Column L (10) minus Column ? = 10 (no x's in this Row)

Expected Results (Returned in Column "O" relevant Row Number):
Row5 = 4
Row6 = 0
Row7 = 1
Row8 = 5
Row9 = 0
Row10= 9
Row11= 3
Row12=10

Can you see a way to tweak the Formula to provide the correct count for
each Row?

Further assistance appreciated.

Thanks
Sam

--
Message posted via http://www.officekb.com
  #4   Report Post  
Old April 16th 05, 11:42 PM
Aladin Akyurek
 
Posts: n/a
Default

=IF(COUNTIF(C5:L5,"x"),COLUMNS(C5:L5)-MATCH(2,1/(C5:L5="x")),0)

which needs to be confirmed with+control+enter.
  #5   Report Post  
Old April 17th 05, 12:26 AM
Domenic
 
Posts: n/a
Default

Hi Sam,

That's probably because your formulas within your range of cells return
a "" (null string) when their particular condition evaluates to false.
In this case, we can tweak Aladin's formula...

=COLUMNS(C5:L5)-IF(COUNTIF(C5:L5,"x"),MATCH(2,1/(C5:L5="x")))

Hope this helps!

In article ,
"Sam via OfficeKB.com" wrote:

Hi Domenic,

Thank you for reply. I've tried your suggested Formula below:

=10-IF(COUNTIF(C5:L5,"x"),LOOKUP(REPT("z",255),C5:L5,C OLUMN(C5:L5)-COLUMN
(C5)+1))

The Formula only produces the correct answer for Rows when there a
No x's - returns correct answer of 10 or when an "x" is in last column -
Column L - returns correct answer of 0.

The Formula returns an incorrect 0 (zero) if the LAST "x" in the Row is in
any other column position besides the LAST Column "L".

From the example data below - the Formula should produce 1 (one) in Row 7
but instead it returns an incorrect 0 (zero).

The Row consists of 10 Columns - from "C" to "L", first Row is Row 5. The
individual Cells either house an "x" or is blank (empty).

My data Range:
1st Column = "C"
2nd Column = "D"
3rd Column = "E"
4th Column = "F"
5th Column = "G"
6th Column = "H"
7th Column = "I"
8th Column = "J"
9th Column = "K"
10th Column= "L" (LAST Column)

Data:
Row5 "x" in Column C, G, H
Row6 "x" in Column K, L
Row7 "x" in Column K
Row8 "x" in Column G
Row9 "x" in Column L
Row10"x" in Column C
Row11"x" in Column C, G, I
Row12"x" in Column - (no x's in this Row)

Calculation Required:
Find last NonBlank Cell in each Row, then subtract that cells Column Number
from Last Column Number in data Range.

Row5 = Column L (10) minus Column H (6) = 4
Row6 = Column L (10) minus Column L (10)= 0
Row7 = Column L (10) minus Column K (9) = 1
Row12= Column L (10) minus Column ? = 10 (no x's in this Row)

Expected Results (Returned in Column "O" relevant Row Number):
Row5 = 4
Row6 = 0
Row7 = 1
Row8 = 5
Row9 = 0
Row10= 9
Row11= 3
Row12=10

Can you see a way to tweak the Formula to provide the correct count for
each Row?

Further assistance appreciated.

Thanks
Sam



  #6   Report Post  
Old April 17th 05, 12:30 AM
Domenic
 
Posts: n/a
Default

Sorry, I forgot to mention that the formula needs to be confirmed with
CONTROL+SHIFT+ENTER.

In article ,
Domenic wrote:

Hi Sam,

That's probably because your formulas within your range of cells return
a "" (null string) when their particular condition evaluates to false.
In this case, we can tweak Aladin's formula...

=COLUMNS(C5:L5)-IF(COUNTIF(C5:L5,"x"),MATCH(2,1/(C5:L5="x")))

Hope this helps!

  #7   Report Post  
Old April 17th 05, 12:34 AM
Sam via OfficeKB.com
 
Posts: n/a
Default

Hi Aladin,

Thank you for reply. The Formula returns the correct result for all Rows
where there is an "x" in one or more columns. Unfortunately, where there
are no "x"'s in the Row it should return a count of 10 but returns 0 (zero).

=IF(COUNTIF(C5:L5,"x"),COLUMNS(C5:L5)-MATCH(2,1/(C5:L5="x")),0)
which needs to be confirmed with+control+enter

Any further help much appreciated.

Thanks
Sam

--
Message posted via http://www.officekb.com
  #8   Report Post  
Old April 17th 05, 12:43 AM
Sam via OfficeKB.com
 
Posts: n/a
Default

Hi Domenic,

Thank you for further assistance.

=COLUMNS(C5:L5)-IF(COUNTIF(C5:L5,"x"),MATCH(2,1/(C5:L5="x")))

Your tweaked version of Aladin's Formula has provided the correct results
for all Rows including the Rows where there is no "x".

Much appreciated.

Thanks
Sam

--
Message posted via http://www.officekb.com
  #9   Report Post  
Old April 17th 05, 12:45 AM
Aladin Akyurek
 
Posts: n/a
Default

Sam via OfficeKB.com wrote:
Hi Aladin,

Thank you for reply. The Formula returns the correct result for all Rows
where there is an "x" in one or more columns. Unfortunately, where there
are no "x"'s in the Row it should return a count of 10 but returns 0 (zero).

=IF(COUNTIF(C5:L5,"x"),COLUMNS(C5:L5)-MATCH(2,1/(C5:L5="x")),0)
which needs to be confirmed with+control+enter

Any further help much appreciated.

Thanks
Sam


I didn't notice you you wanted 10 for that. See the tweaked version in
Domenic's reply,
  #10   Report Post  
Old April 17th 05, 12:52 AM
Sam via OfficeKB.com
 
Posts: n/a
Default

Hi Aladin,

Domenic has provided a tweaked version of your Formula and it provides the
correct results for all Rows including those without "x"'s.

=COLUMNS(C5:L5)-IF(COUNTIF(C5:L5,"x"),MATCH(2,1/(C5:L5="x")))

Much appreciated. Thank you for assistance.

Thanks
Sam

--
Message posted via http://www.officekb.com


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"count if" function based on value of another cell Anauna Excel Worksheet Functions 3 February 24th 05 06:33 PM
I want to return "0" based on another cell CM Excel Worksheet Functions 2 February 17th 05 02:46 PM
formula to return the value of a cell based on a looked up true reference sarah Excel Worksheet Functions 2 February 2nd 05 08:15 PM
How to make a cell return the formatted value in a text string (i. n.almeida Excel Worksheet Functions 3 February 2nd 05 01:59 PM
Can vlookup return multiple matches in a single cell? cchristensen Excel Discussion (Misc queries) 3 December 2nd 04 01:56 AM


All times are GMT +1. The time now is 10:20 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017