Sum duplicated values and quantity

[deraco123]

Hi everybody

I am trying through whole weekend to create autocalculation for following spreadsheet.
I want to count all duplicates in spreadsheet.

<TABLE style="WIDTH: 330pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=439 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 121pt; mso-width-source: userset; mso-width-alt: 5888" width=161><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5485" width=150><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Qty</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 121pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=161>Product</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 113pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=150>Duplicated values</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Qty Sum</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">house</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow">house</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow" align=right>9</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">computer</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow">computer</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow" align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">house</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow">window </TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow" align=right>11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">computer</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow">key</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow" align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">house</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> </TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>10</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">window</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> </TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">key</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> </TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">window</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> </TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow"> </TD></TR></TBODY></TABLE>
Have anyone idea how which formula to use in order to get Numbers in column D automatically?

Thanks

deraco

 

[hail11]

put this in your cell you want to count


=COUNTIF(B5:B9,"=bob")

b5:b9 will be where all the possible words will be
bob will be what you want it to find and count

sorry never saw the numbers beside

 

[Yogi Anand]

Hi deraco123:

Welcome to MrExcel Board!

Try the SUMIF function as in ...

<html><head><title>Excel Jeanie HTML</title></head><body>

Excel Workbook
	A	B	C	D
1	Qty	Product	Duplicated values	Qty Sum
2	1	house	house	9
3	1	computer	computer	3
4	3	house	window	11
5	2	computer	key	1
6	5	house
7	10	window
8	1	key
9	1	window
Sheet6

</body></html>

 

[deraco123]

Hi

thank you for interest guys.
I am sorry I wrote this post late in the night and did not describe it very well.
I try it again.

I want to create worksheet where I will paste (from web source) product names into column B.
Then I will paste Quantity of this product into column A, (about 100-300 rows).
Now, what i need is some kind of formula which will put all products into column D (without duplicates) and their sum value into column C

Any thoughts how to do this?

Thanks

 

[Aladin Akyurek]

Hi

thank you for interest guys.
I am sorry I wrote this post late in the night and did not describe it very well.
I try it again.

I want to create worksheet where I will paste (from web source) product names into column B.
Then I will paste Quantity of this product into column A, (about 100-300 rows).
Now, what i need is some kind of formula which will put all products into column D (without duplicates) and their sum value into column C

Any thoughts how to do this?

Thanks

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" align=right width=64 x:fmla='=SUM(IF(FREQUENCY(IF(B3:B10<>"",MATCH("~"&B3:B10&"",B3:B10&"",0)),ROW(B3:B10)-ROW(B3)+1),1))' x:arrayrange="C1" x:num>4</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: white" width=64 height=34>Qty</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Product</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Duplicated values</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Qty Sum</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>1</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>house</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:fmla='=IF(ROWS($C$3:C3)<=$C$1,INDEX($B$3:$B$10,SMALL(IF(FREQUENCY(IF($B$3:$B$10<>"",MATCH("~"&$B$3:$B$10&"",$B$3:$B$10&"",0)),ROW($B$3:$B$10)-ROW($B$3)+1),ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($C$3:C3))),"")' x:arrayrange="C3">house</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:fmla='=IF(C3="","",SUMIF($B$3:$B$10,C3,$A$3:$A$10))' x:num>9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>1</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>computer</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:fmla='=IF(ROWS($C$3:C4)<=$C$1,INDEX($B$3:$B$10,SMALL(IF(FREQUENCY(IF($B$3:$B$10<>"",MATCH("~"&$B$3:$B$10&"",$B$3:$B$10&"",0)),ROW($B$3:$B$10)-ROW($B$3)+1),ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($C$3:C4))),"")' x:arrayrange="C4">computer</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:fmla='=IF(C4="","",SUMIF($B$3:$B$10,C4,$A$3:$A$10))' x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>3</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>house</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:fmla='=IF(ROWS($C$3:C5)<=$C$1,INDEX($B$3:$B$10,SMALL(IF(FREQUENCY(IF($B$3:$B$10<>"",MATCH("~"&$B$3:$B$10&"",$B$3:$B$10&"",0)),ROW($B$3:$B$10)-ROW($B$3)+1),ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($C$3:C5))),"")' x:arrayrange="C5">window</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:fmla='=IF(C5="","",SUMIF($B$3:$B$10,C5,$A$3:$A$10))' x:num>11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>2</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>computer</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:fmla='=IF(ROWS($C$3:C6)<=$C$1,INDEX($B$3:$B$10,SMALL(IF(FREQUENCY(IF($B$3:$B$10<>"",MATCH("~"&$B$3:$B$10&"",$B$3:$B$10&"",0)),ROW($B$3:$B$10)-ROW($B$3)+1),ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($C$3:C6))),"")' x:arrayrange="C6">key</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:fmla='=IF(C6="","",SUMIF($B$3:$B$10,C6,$A$3:$A$10))' x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>5</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>house</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:str="" x:fmla='=IF(ROWS($C$3:C7)<=$C$1,INDEX($B$3:$B$10,SMALL(IF(FREQUENCY(IF($B$3:$B$10<>"",MATCH("~"&$B$3:$B$10&"",$B$3:$B$10&"",0)),ROW($B$3:$B$10)-ROW($B$3)+1),ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($C$3:C7))),"")' x:arrayrange="C7"> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:str="" x:fmla='=IF(C7="","",SUMIF($B$3:$B$10,C7,$A$3:$A$10))'> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>10</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>window</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:str="" x:fmla='=IF(ROWS($C$3:C8)<=$C$1,INDEX($B$3:$B$10,SMALL(IF(FREQUENCY(IF($B$3:$B$10<>"",MATCH("~"&$B$3:$B$10&"",$B$3:$B$10&"",0)),ROW($B$3:$B$10)-ROW($B$3)+1),ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($C$3:C8))),"")' x:arrayrange="C8"> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:str="" x:fmla='=IF(C8="","",SUMIF($B$3:$B$10,C8,$A$3:$A$10))'> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>1</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>key</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:str="" x:fmla='=IF(ROWS($C$3:C9)<=$C$1,INDEX($B$3:$B$10,SMALL(IF(FREQUENCY(IF($B$3:$B$10<>"",MATCH("~"&$B$3:$B$10&"",$B$3:$B$10&"",0)),ROW($B$3:$B$10)-ROW($B$3)+1),ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($C$3:C9))),"")' x:arrayrange="C9"> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:str="" x:fmla='=IF(C9="","",SUMIF($B$3:$B$10,C9,$A$3:$A$10))'> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>1</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>window</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" x:str="" x:fmla='=IF(ROWS($C$3:C10)<=$C$1,INDEX($B$3:$B$10,SMALL(IF(FREQUENCY(IF($B$3:$B$10<>"",MATCH("~"&$B$3:$B$10&"",$B$3:$B$10&"",0)),ROW($B$3:$B$10)-ROW($B$3)+1),ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($C$3:C10))),"")' x:arrayrange="C10"> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64 x:str="" x:fmla='=IF(C10="","",SUMIF($B$3:$B$10,C10,$A$3:$A$10))'> </TD></TR></TBODY></TABLE>

A2:B10 houses the data.

C1:

Control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(IF(B3:B10<>"",MATCH("~"&B3:B10&"",B3:B10&"",0)),ROW(B3:B10)-ROW(B3)+1),1))

C3:

Control+shift+enter, not just enter...

=IF(ROWS($C$3:C3)<=$C$1,INDEX($B$3:$B$10,
   SMALL(IF(FREQUENCY(IF($B$3:$B$10<>"",
    MATCH("~"&$B$3:$B$10&"",$B$3:$B$10&"",0)),
      ROW($B$3:$B$10)-ROW($B$3)+1),
       ROW($B$3:$B$10)-ROW($B$3)+1),ROWS($C$3:C3))),"")

and copy down.

D3, copy down:

=IF(C3="","",SUMIF($B$3:$B$10,C3,$A$3:$A$10))

 

[deraco123]

Hi Aladin
your miraculous formula works
I had to replace all commas by semicolumn though.
So if somebody in future is interested in this thread:
C1:
=SUM(IF(FREQUENCY(IF(B3:B10<>"";MATCH("~"&B3:B10&"";B3:B10&"";0));ROW(B3:B10)-ROW(B3)+1);1))

ctrl+shift+enter
C3:

=IF(ROWS($C$3:C3)<=$C$1;INDEX($B$3:$B$10;SMALL(IF(FREQUENCY(IF($B$3:$B$10<>"";MATCH("~"&$B$3:$B$10&"";$B$3:$B$10&"";0));ROW($B$3:$B$10)-ROW($B$3)+1);ROW($B$3:$B$10)-ROW($B$3)+1);ROWS($C$3:C3)));"")

ctrl+shift+enter

The rest as per Aladin
@Aladin, many many thanks for your help...looking at your formula I realized that there is a lot to learn for me
Thanks

 

[PaddyD]

a pivot table would also do it...