Find Duplicate with formula and make idividual list

[Navi_G]

Hi,


In Illustration i see duplicates and i want to make a list with formula.
other if possible to sum of list idividual with same column like

Question Table

Invioce No. Total
11760 $90.03
11105 $77.63
11173 $74.92
11871 $83.29
11334 $96.34
11908 $61.27
11961 $67.38
11105 $61.66
11334 $95.72
11766 $69.41
11468 $86.76
11173 $72.88
11306 $69.90
11905 $68.37
11306 $54.00


Desired Result Table

Invioce No.Sum Total
11105 $139.29
11173 $147.80
11334 $192.06
11306 $123.90

Please help on Duplicate and unique list formula and sum of duplicate of total.
I think you better understand my problem.

Thanks
Navi_G

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​
1​	Invioce No.	Total		Invioce No.	Sum Total
2​	11760​	$90.03​		11760​	90.03​
3​	11105​	$77.63​		11105​	139.29​
4​	11173​	$74.92​		11173​	147.8​
5​	11871​	$83.29​		11871​	83.29​
6​	11334​	$96.34​		11334​	192.06​
7​	11908​	$61.27​		11908​	61.27​
8​	11961​	$67.38​		11961​	67.38​
9​	11105​	$61.66​		11766​	69.41​
10​	11334​	$95.72​		11468​	86.76​
11​	11766​	$69.41​		11306​	123.9​
12​	11468​	$86.76​		11905​	68.37​
13​	11173​	$72.88​
14​	11306​	$69.90​
15​	11905​	$68.37​
16​	11306​	$54.00​

Sheet: Sheet70

Array formula in cell D2:
=INDEX($A$2:$A$16,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$16),0))

Formula in cell E2:
=SUMIF($A$2:$A$16,D2,$B$2:$B$16)

 

[Navi_G]

Thanks For your reply

I understand easily thank and please guide me on unique value list please.

 

[Navi_G]

=INDEX($A$2:$A$16,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$16),0))

Formula is not working please check and confirm.

 

[Aladin Akyurek]

Perhaps you did not apply control+shift+enter to the suggested formula.

Another way would be the following.

Book1
	A	B	C	D	E
1	Invioce No.	Total		11
2	11760	90.03		Invioce No.	Sum Total
3	11105	77.63		11105	139.29
4	11173	74.92		11173	147.8
5	11871	83.29		11306	123.9
6	11334	96.34		11334	192.06
7	11908	61.27		11468	86.76
8	11961	67.38		11760	90.03
9	11105	61.66		11766	69.41
10	11334	95.72		11871	83.29
11	11766	69.41		11905	68.37
12	11468	86.76		11908	61.27
13	11173	72.88		11961	67.38
14	11306	69.9
15	11905	68.37
16	11306	54
Sheet1

In D1 just enter:

=SUM(IF(FREQUENCY(A2:A16,A2:A16),1))

In D3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$3:D3)>$D$1,"",MIN(IF(ISNA(MATCH($A$2:$A$16,$D$2:D2,0)),$A$2:$A$16)))

Control+shift+enter: Press down the control and the shift keys while you hit enter key. If done correctly, Excel itself puts a pair of { and } around the formula in recognition.

In E3 just enter and copy down:

=IF($D3="","",SUMIFS($B$2:$B$16,$A$2:$A$16,$D3))

 

[Navi_G]

Perhaps you did not apply control+shift+enter to the suggested formula.

Another way would be the following.

	A	B	C	D	E
1	Invioce No.	Total		11
2	11760	90.03		Invioce No.	Sum Total
3	11105	77.63		11105	139.29
4	11173	74.92		11173	147.8
5	11871	83.29		11306	123.9
6	11334	96.34		11334	192.06
7	11908	61.27		11468	86.76
8	11961	67.38		11760	90.03
9	11105	61.66		11766	69.41
10	11334	95.72		11871	83.29
11	11766	69.41		11905	68.37
12	11468	86.76		11908	61.27
13	11173	72.88		11961	67.38
14	11306	69.9
15	11905	68.37
16	11306	54

<COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

In D1 just enter:

=SUM(IF(FREQUENCY(A2:A16,A2:A16),1))

In D3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$3:D3)>$D$1,"",MIN(IF(ISNA(MATCH($A$2:$A$16,$D$2:D2,0)),$A$2:$A$16)))

Control+shift+enter: Press down the control and the shift keys while you hit enter key. If done correctly, Excel itself puts a pair of { and } around the formula in recognition.

In E3 just enter and copy down:

=IF($D3="","",SUMIFS($B$2:$B$16,$A$2:$A$16,$D3))

Hi,

Dear Aladin Akyurek if i want only duplicate values and and ignor single values then what formula is rquired.

Thanks For your expert opinion.

Thanks
Navi_G

 

[Aladin Akyurek]

Hi,

Dear Aladin Akyurek if i want only duplicate values and and ignor single values then what formula is rquired.

Thanks For your expert opinion.

Thanks
Navi_G

Book1
	A	B	C	D	E
1	Invioce No.	Total		4
2	11760	90.03		Invioce No.	Sum Total
3	11105	77.63		11105	139.29
4	11173	74.92		11173	147.8
5	11871	83.29		11334	192.06
6	11334	96.34		11306	123.9
7	11908	61.27
8	11961	67.38
9	11105	61.66
10	11334	95.72
11	11766	69.41
12	11468	86.76
13	11173	72.88
14	11306	69.9
15	11905	68.37
16	11306	54
Sheet1

In D1 just enter:

=SUM(IF(FREQUENCY(A2:A16,A2:A16)>1,1))

In D3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$3:D3)>$D$1,"",INDEX($A$2:$A$16,SMALL(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$16),MATCH($A$2:$A$16,$A$2:$A$16,0)),ROW($A$2:$A$16)-ROW($A$2)+1)>1,ROW($A$2:$A$16)-ROW($A$2)+1),ROWS($D$3:D3))))

In E3 just enter and copy down:

=IF($D3="","",SUMIFS($B$2:$B$16,$A$2:$A$16,$D3))

 

[Navi_G]

=IF(ROWS($D$3:D3)>$D$1,"",INDEX($A$2:$A$16,SMALL(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$16),MATCH($A$2:$A$16,$A$2:$A$16,0)),ROW($A$2:$A$16)-ROW($A$2)+1)>1,ROW($A$2:$A$16)-ROW($A$2)+1),ROWS($D$3:D3))))

Dear Sir thanks alot For solved problem.

Formula Structure is little bit tough and not undstand.its ok thanks.

Regards & thanks
Navi_G

 

[Aladin Akyurek]

@ Navi_G

Glad to help.