software

i have the following data
Name Score
AB 10
CD 15
AB 05
EF 20
CD 30
AB 50
I want to dispaly only the unique records with their total score.(e.g. AB
65)
Pls help, i am trying since last three days.What you need is a Pivot Table.

1) With any one cell of your table selected, from the Data menu, choose
quot;Pivot Table amp; Pivot Chart Reportquot;
2) Click the next button twice
3) Click the quot;Layoutquot; button.
4) Use the mouse to drag the quot;Namequot; (from the right) onto the quot;ROWquot; area.
5) Drag the quot;Scorequot; onto the quot;Dataquot; area
6) Click OK
7) Click Finish

Regards,
Edwin Tam

www.vonixx.comquot;Nadquot; wrote:

gt; i have the following data
gt; Name Score
gt; AB 10
gt; CD 15
gt; AB 05
gt; EF 20
gt; CD 30
gt; AB 50
gt; I want to dispaly only the unique records with their total score.(e.g. AB
gt; 65)
gt; Pls help, i am trying since last three days.
gt;

Hello Nad,

i believe, you can create a PivotTable, that will have the desired result
(every occurance is shown only once and their Score can be summed up.

in Office 2003, Data menu/Pivot table wizard, then follow the wizards' step
(help is available) and the dragamp;drop the Name field to Row, the Score field
to Data and voilá :-)

alternatively, in case you have an additional list of every occurance, you
could use the SUMIF function as well.

please, let me know, if you need further help.

Best regards,
ANdras
(Hungary)

quot;Nadquot; wrote:

gt; i have the following data
gt; Name Score
gt; AB 10
gt; CD 15
gt; AB 05
gt; EF 20
gt; CD 30
gt; AB 50
gt; I want to dispaly only the unique records with their total score.(e.g. AB
gt; 65)
gt; Pls help, i am trying since last three days.
gt;

Not precisely what you asked for, but a nice display of the data would be to
alphabetize on the name column, then do Data gt; Subtotals gt; At each change in
: NAME gt; Use function: SUM gt; Add subtotal to: check the SCORE box gt; OK

Vaya con Dios,
Chuck, CABGx3quot;Nadquot; gt; wrote in message
...
gt; i have the following data
gt; Name Score
gt; AB 10
gt; CD 15
gt; AB 05
gt; EF 20
gt; CD 30
gt; AB 50
gt; I want to dispaly only the unique records with their total score.(e.g. AB
gt; 65)
gt; Pls help, i am trying since last three days.
gt;
Hi Nad,
Assuming the range of Names are in column A and scores are in column B
then the following formula in column C will return the names but only
in the first row that they appear eg AB in row 2 but not again in rows
4 or 7...

=IF(COUNTIF(A$2:A$65536,A2)-COUNTIF(A2:A$65536,A2)=0,A2,quot;quot;)

There total scores can be returned in column D using...

=IF(C2lt;gt;quot;quot;,SUMPRODUCT(($A$2:$A$65536=A2)*$B$2:$B$6 5536),quot;quot;)

You can then apply auto filter to hide the blank rows then copy and
paste to wherever.

Ken Johnson
Nad Wrote:
gt; i have the following data
gt; Name Score
gt; AB 10
gt; CD 15
gt; AB 05
gt; EF 20
gt; CD 30
gt; AB 50
gt; I want to dispaly only the unique records with their total score.(e.g.
gt; AB
gt; 65)
gt; Pls help, i am trying since last three days.

Check this site for sumproduct,

www.xldynamic.com/source/xld.SUMPRODUCT.html--
davesexcel------------------------------------------------------------------------
davesexcel's Profile: www.excelforum.com/member.php...oamp;userid=31708
View this thread: www.excelforum.com/showthread...hreadid=533259thanks for your help
I don't want to use Pivot table.Also i dont want to use Sumif function
because i have more than 100 names because i have to write sumif function for
every name.

i want to display the sum of score of particular name in adjacent column.
(i can delete the score column and want to display total score column)

quot;ANdrasquot; wrote:

gt; Hello Nad,
gt;
gt; i believe, you can create a PivotTable, that will have the desired result
gt; (every occurance is shown only once and their Score can be summed up.
gt;
gt; in Office 2003, Data menu/Pivot table wizard, then follow the wizards' step
gt; (help is available) and the dragamp;drop the Name field to Row, the Score field
gt; to Data and voilá :-)
gt;
gt; alternatively, in case you have an additional list of every occurance, you
gt; could use the SUMIF function as well.
gt;
gt; please, let me know, if you need further help.
gt;
gt; Best regards,
gt; ANdras
gt; (Hungary)
gt;
gt; quot;Nadquot; wrote:
gt;
gt; gt; i have the following data
gt; gt; Name Score
gt; gt; AB 10
gt; gt; CD 15
gt; gt; AB 05
gt; gt; EF 20
gt; gt; CD 30
gt; gt; AB 50
gt; gt; I want to dispaly only the unique records with their total score.(e.g. AB
gt; gt; 65)
gt; gt; Pls help, i am trying since last three days.
gt; gt;

If you make the 2nd argument of the sumif function a cell reference rather
than a fixed name, you can autofill the formula and not have to enter it 100
times. That is, instead of =sumif(A:A,quot;Nadquot;,B:B), use =sumif(A:A,E2,B:B) if
the list of unique names begins in cell E2. Then just copy that formula from
F2 all the way down to the end of your list of unique names.
(FWIW, the Pivot Table would have been my suggestion as well; it will
determine all the unique names and total the associated scores).
--Bruce

quot;Nadquot; wrote:

gt; thanks for your help
gt; I don't want to use Pivot table.Also i dont want to use Sumif function
gt; because i have more than 100 names because i have to write sumif function for
gt; every name.
gt;
gt; i want to display the sum of score of particular name in adjacent column.
gt; (i can delete the score column and want to display total score column)
gt;
gt; quot;ANdrasquot; wrote:
gt;
gt; gt; Hello Nad,
gt; gt;
gt; gt; i believe, you can create a PivotTable, that will have the desired result
gt; gt; (every occurance is shown only once and their Score can be summed up.
gt; gt;
gt; gt; in Office 2003, Data menu/Pivot table wizard, then follow the wizards' step
gt; gt; (help is available) and the dragamp;drop the Name field to Row, the Score field
gt; gt; to Data and voilá :-)
gt; gt;
gt; gt; alternatively, in case you have an additional list of every occurance, you
gt; gt; could use the SUMIF function as well.
gt; gt;
gt; gt; please, let me know, if you need further help.
gt; gt;
gt; gt; Best regards,
gt; gt; ANdras
gt; gt; (Hungary)
gt; gt;
gt; gt; quot;Nadquot; wrote:
gt; gt;
gt; gt; gt; i have the following data
gt; gt; gt; Name Score
gt; gt; gt; AB 10
gt; gt; gt; CD 15
gt; gt; gt; AB 05
gt; gt; gt; EF 20
gt; gt; gt; CD 30
gt; gt; gt; AB 50
gt; gt; gt; I want to dispaly only the unique records with their total score.(e.g. AB
gt; gt; gt; 65)
gt; gt; gt; Pls help, i am trying since last three days.
gt; gt; gt;

Pivot table is the best option... It summarises data very well and can be
refreshed at any time. Double click on any of the data cell to get the
records that built up that sum. This is one of the best functions of the
pivot - i.e., a drill down.
quot;Nadquot; wrote:

gt; i have the following data
gt; Name Score
gt; AB 10
gt; CD 15
gt; AB 05
gt; EF 20
gt; CD 30
gt; AB 50
gt; I want to dispaly only the unique records with their total score.(e.g. AB
gt; 65)
gt; Pls help, i am trying since last three days.
gt;

Hi Ken,
Its amazing. I got my result exactly the way i want.
Thank U very much indeed.
Regards
Nad

quot;Ken Johnsonquot; wrote:

gt; Hi Nad,
gt; Assuming the range of Names are in column A and scores are in column B
gt; then the following formula in column C will return the names but only
gt; in the first row that they appear eg AB in row 2 but not again in rows
gt; 4 or 7...
gt;
gt; =IF(COUNTIF(A$2:A$65536,A2)-COUNTIF(A2:A$65536,A2)=0,A2,quot;quot;)
gt;
gt; There total scores can be returned in column D using...
gt;
gt; =IF(C2lt;gt;quot;quot;,SUMPRODUCT(($A$2:$A$65536=A2)*$B$2:$B$6 5536),quot;quot;)
gt;
gt; You can then apply auto filter to hide the blank rows then copy and
gt; paste to wherever.
gt;
gt; Ken Johnson
gt;
gt;