need help for formula

[chobanne]

Hello all i got 2 columns A and B , with random values in them.

I need in column C to put all of the values ​​that appear in the column A with no repeat, and in column D to put the sum of all values in column B which correspond to the values ​​of a column A. I need that to be formula conection, not advanced filter.

example

A B C D
1 3 1 4
2 5 2 12
3 6 3 8
2 7 4 10
1 1
4 6
3 2
4 4

Thank you this is very important to me, because i always have problem with advanced filter

 

[poiu]

Hi Chobanne,

Why is the bottom value in column C "4"? should it not be blank because 2 is already present in the 2nd row of column A?

I don't follow the logic in column D. Should D equal A plus B? It's that in the first row but not the other rows.

Poiu

 

[chobanne]

I dont know what you understand, but in C i want to show every value that exist in column A (thats 1,2,3 and 4) and in D i want to get sum of values in B which are next to this values in column A (thats for 1(3+1=4),2(5+7=12),3(6+2=8) and 4(6+4=10))

 

[FDibbins]

In C2, copied down...
=IFERROR(INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$9),0,0),0)),"")

Then in D2, copied down...
=SUMIF($A$2:$A$9,E2,$B$2:$B$9)

 

[poiu]

OK I understand column C now, but still now column D - please could you retry?

edit: ignore me - FDibbins beat me to it!

 

[chobanne]

In C2, copied down...
=IFERROR(INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$9),0,0),0)),"")

Then in D2, copied down...
=SUMIF($A$2:$A$9,E2,$B$2:$B$9)

first formula is good but second dont, whats E2?

 

[FDibbins]

Apologies, I had thise 2 formulas in E and F, so that I vcould keep your original data to compare with.

It should have read...
=SUMIF($A$2:$A$9,C2,$B$2:$B$9)

 

[fredlo2008]

Hello,

Here is a possible solution. I found this amazing formula for unique values online at Create a unique distinct alphabetically sorted list, extracted from a column in excel | Get Digital Help - Microsoft Excel resource

Note that the formula uses a few named ranges for simplicity, make sure you add those, If you want make them dynamic with this formulas:

List=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)

Values=OFFSET(Sheet2!$B$1,1,0,COUNTA(Sheet2!$B:$B)-1,1)

Also the formula in C1:C9 is an array formula press ctr+Shift+enter to enter it.

Here is the main formula for the unique values:

=IF(COUNT(IF(MATCH(List,List,0)=(ROW(List)-ROW(List_Start)+1),COUNTIF(List,"<"&List)+1,""))>(ROW(List)-ROW(List_Start)),INDEX(List,MATCH(SMALL(IF(MATCH(List,List,0)=(ROW(List)-ROW(List_Start)+1),COUNTIF(List,"<"&List)+1,""),(ROW(List)-ROW(List_Start)+1)),IF(MATCH(List,List,0)=(ROW(List)-ROW(List_Start)+1),COUNTIF(List,"<"&List)+1,""),0)),"")

This is the formula for the calculations:

=IF(C2="","",SUMPRODUCT(Values,--(List=C2)))

I have attached a copy of the work book in case you have questions https://dl.dropboxusercontent.com/u/30987064/Solution.xlsx

Thanks

 

[chobanne]

Thats perfect, works perffect. Thank you very much