Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.


For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:


Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------


any way just using the excel inside or do i have to use visua basic?

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters -- Tab, semicolon, comma, space,
other.

"Dave Peterson" wrote:

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson

First, a stupid typo on my part!

Data|Text to columns
should read
Data|Pivottable
(stupid fingers sometimes type what they want!)

Select your range on the detail tab to create the pivottable.

Select the date field on the pivottable to group by year.

kyoshirou wrote:

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters -- Tab, semicolon, comma, space,
other.

"Dave Peterson" wrote:

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson


--

Dave Peterson

I still dont get it.

I am thinking is it possible to add in some validation or forumula inside
the B2, C2, D2, etc to compulate the total instead. It seem to work faster in
this way. Am i right? Please advice~

A B C D E F G H
1 Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
2 A
3 B
4 C
5 D
6 E
7 F
8 G
--------------------------------------------------------
9 Total
--------------------------------------------------------

"Dave Peterson" wrote:

First, a stupid typo on my part!

Data|Text to columns
should read
Data|Pivottable
(stupid fingers sometimes type what they want!)

Select your range on the detail tab to create the pivottable.

Select the date field on the pivottable to group by year.

kyoshirou wrote:

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters -- Tab, semicolon, comma, space,
other.

"Dave Peterson" wrote:

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson


--

Dave Peterson

What don't you get and what did you try that you had trouble with?

You could get the sum of all the markers for 2000 in class A:
=sumproduct(--(year(b1:b100)=2000),--(a1:a100="A"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



kyoshirou wrote:

I still dont get it.

I am thinking is it possible to add in some validation or forumula inside
the B2, C2, D2, etc to compulate the total instead. It seem to work faster in
this way. Am i right? Please advice~

A B C D E F G H
1 Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
2 A
3 B
4 C
5 D
6 E
7 F
8 G
--------------------------------------------------------
9 Total
--------------------------------------------------------

"Dave Peterson" wrote:

First, a stupid typo on my part!

Data|Text to columns
should read
Data|Pivottable
(stupid fingers sometimes type what they want!)

Select your range on the detail tab to create the pivottable.

Select the date field on the pivottable to group by year.

kyoshirou wrote:

Hi Dave,

I have to select my range from the detail tab or new tab? I have to select
every single column is it?
I am unable to find my Layout button, followed by
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

I only can see something like Delimiters -- Tab, semicolon, comma, space,
other.

"Dave Peterson" wrote:

Select your range (Include the header row)
Data|Text to columns
follow the wizard until you get to a step with a Layout button on it.
click that layout button
drag the Class button to the row field
drag the date button to the column field
drag the Marks button and drag to the data field

Finish up the wizard.

Rightclick on any of the dates.
select Group and Show Detail
Then check Group
and group by years (and remove any that excel guessed)

With your sample data, I ended up with:

Sum of Makrs Date
Class 2001 2002 2003 2004 2005 2006 Grand Total
A 50 10 60
B 30 30
C 30 30
D 40 20 60
E 20 20
F 50 40 90
G 50 20 20 90
Grand Total 90 50 80 60 50 50 380


kyoshirou wrote:

Hi All,
Is me again.. having some problems.
Do help me, thanks!

Trying to create something like this:
Let say I have a set of records running disorder from class
A,B,C,D,E,F,G,etc and marks range from 10,20,30,40,50. from years
2000,2001,2002,2003,2004,2005,2006.

Now, i trying to create a new table (mayb in a new tag) to store combine
total marks for that particular year for each A,B,C,D,E,F,G.

For example:

Class Date Makrs
A 5-aug-06 10
A 5-aug-05 20
B 5-aug-03 30
C 5-aug-02 30
D 5-aug-01 40
D 5-aug-04 20
G 5-aug-06 20
A 5-aug-05 30
E 5-aug-06 20
F 5-aug-04 40
G 5-aug-02 20
G 5-aug-01 50
F 5-aug-03 50

after sorting the table can look like this:

Year |2000|2001|2002|2003|2004|2005|2006|
-------------------------------------------------------
A
B
C
D
E
F
G
--------------------------------------------------------
Total
--------------------------------------------------------

any way just using the excel inside or do i have to use visua basic?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson