Find First and Last Row Number in Column
 

Hi All,

I have the following data which will always be sorted/grouped. I
basically want to sum all the A's, B's, and C's values (like a
subtotal)

Column Value
A 2
A 3
A 1
B 3
B 4
B 5
C 6
C 1
C 9

I want to find the the First and last row number A and also for B, so
Start A = 1, End A = 3
Start B = 4, End B = 6
Start C = 7, End C = 9

This issue I have is the column values can differ. For example,
Column
A
A
A
C
C
C

So,
Start A = 1, End A = 3
Start C = 4, End C = 6

Since I know the start and end of each grouping, I can then sum them.
If the column alwas had A,B,and C, then I know how to do this,
however, since it is dynamic, I am not sure how to write code dynamic
enough to handle this.

TIA

Find First and Last Row Number in Column
 

If the end results are totaling numbers associated with A, B, and C, why not
use a pivot table?

This site will get you started:
http://www.contextures.com/xlPivot01.html


Don Pistulka


"CST" wrote in message
om...
Hi All,

I have the following data which will always be sorted/grouped. I
basically want to sum all the A's, B's, and C's values (like a
subtotal)

Column Value
A 2
A 3
A 1
B 3
B 4
B 5
C 6
C 1
C 9

I want to find the the First and last row number A and also for B, so
Start A = 1, End A = 3
Start B = 4, End B = 6
Start C = 7, End C = 9

This issue I have is the column values can differ. For example,
Column
A
A
A
C
C
C

So,
Start A = 1, End A = 3
Start C = 4, End C = 6

Since I know the start and end of each grouping, I can then sum them.
If the column alwas had A,B,and C, then I know how to do this,
however, since it is dynamic, I am not sure how to write code dynamic
enough to handle this.

TIA

Find First and Last Row Number in Column
 

sumA =application.Sumif(Columns(1),"A",Columns(2))

SumB = application.Sumif(Columns(1),"B",Columns(2))

SumC = application.Sumif(Columns(1),"C",Columns(2))


will do it without sorting or grouping.

Regards,
Tom Ogilvy

"CST" wrote in message
om...
Hi All,

I have the following data which will always be sorted/grouped. I
basically want to sum all the A's, B's, and C's values (like a
subtotal)

Column Value
A 2
A 3
A 1
B 3
B 4
B 5
C 6
C 1
C 9

I want to find the the First and last row number A and also for B, so
Start A = 1, End A = 3
Start B = 4, End B = 6
Start C = 7, End C = 9

This issue I have is the column values can differ. For example,
Column
A
A
A
C
C
C

So,
Start A = 1, End A = 3
Start C = 4, End C = 6

Since I know the start and end of each grouping, I can then sum them.
If the column alwas had A,B,and C, then I know how to do this,
however, since it is dynamic, I am not sure how to write code dynamic
enough to handle this.

TIA

Find First and Last Row Number in Column
 

If the list is in A1 to A9 and values are in B1:B9

then this formula will do what you ask for.

=SUMPRODUCT((A1:A9="A")*1,B1:B9)

Replace the "A" with "B" to sum the rows with B in column A...

Wendel


"CST" skrev i meddelandet
om...
Hi All,

I have the following data which will always be sorted/grouped. I
basically want to sum all the A's, B's, and C's values (like a
subtotal)

Column Value
A 2
A 3
A 1
B 3
B 4
B 5
C 6
C 1
C 9

I want to find the the First and last row number A and also for B, so
Start A = 1, End A = 3
Start B = 4, End B = 6
Start C = 7, End C = 9

This issue I have is the column values can differ. For example,
Column
A
A
A
C
C
C

So,
Start A = 1, End A = 3
Start C = 4, End C = 6

Since I know the start and end of each grouping, I can then sum them.
If the column alwas had A,B,and C, then I know how to do this,
however, since it is dynamic, I am not sure how to write code dynamic
enough to handle this.

TIA

Find First and Last Row Number in Column
 

Hi,

You might want to use the SUMIF function...
=SUMIF(A:A,"A",B:B) will sum anything in column A that
is "A" using the values in column B.

Hope this helps...

Chong Moua

-----Original Message-----
Hi All,

I have the following data which will always be
sorted/grouped. I
basically want to sum all the A's, B's, and C's values
(like a
subtotal)

Column Value
A 2
A 3
A 1
B 3
B 4
B 5
C 6
C 1
C 9

I want to find the the First and last row number A and
also for B, so
Start A = 1, End A = 3
Start B = 4, End B = 6
Start C = 7, End C = 9

This issue I have is the column values can differ. For
example,
Column
A
A
A
C
C
C

So,
Start A = 1, End A = 3
Start C = 4, End C = 6

Since I know the start and end of each grouping, I can
then sum them.
If the column alwas had A,B,and C, then I know how to do
this,
however, since it is dynamic, I am not sure how to write
code dynamic
enough to handle this.

TIA
.

Find First and Last Row Number in Column
 

Sweet!!! This worked! Thank you sooooooo much!



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!