Find Duplicate with formula and make idividual list

Navi_G

Board Regular
Joined
May 30, 2018
Messages
94
Office Version
  1. 2016
Platform
  1. Windows
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:confused:
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td]Invioce No.[/td][td]Total[/td][td][/td][td]Invioce No.[/td][td]Sum Total[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]
11760​
[/td][td]
$90.03​
[/td][td][/td][td]
11760​
[/td][td]
90.03​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]
11105​
[/td][td]
$77.63​
[/td][td][/td][td]
11105​
[/td][td]
139.29​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]
11173​
[/td][td]
$74.92​
[/td][td][/td][td]
11173​
[/td][td]
147.8​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]
11871​
[/td][td]
$83.29​
[/td][td][/td][td]
11871​
[/td][td]
83.29​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]
11334​
[/td][td]
$96.34​
[/td][td][/td][td]
11334​
[/td][td]
192.06​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td]
11908​
[/td][td]
$61.27​
[/td][td][/td][td]
11908​
[/td][td]
61.27​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8
[/td][td]
11961​
[/td][td]
$67.38​
[/td][td][/td][td]
11961​
[/td][td]
67.38​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9
[/td][td]
11105​
[/td][td]
$61.66​
[/td][td][/td][td]
11766​
[/td][td]
69.41​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10
[/td][td]
11334​
[/td][td]
$95.72​
[/td][td][/td][td]
11468​
[/td][td]
86.76​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11
[/td][td]
11766​
[/td][td]
$69.41​
[/td][td][/td][td]
11306​
[/td][td]
123.9​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12
[/td][td]
11468​
[/td][td]
$86.76​
[/td][td][/td][td]
11905​
[/td][td]
68.37​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13
[/td][td]
11173​
[/td][td]
$72.88​
[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14
[/td][td]
11306​
[/td][td]
$69.90​
[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15
[/td][td]
11905​
[/td][td]
$68.37​
[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16
[/td][td]
11306​
[/td][td]
$54.00​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet70[/td][/tr][/table]

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)
 
Upvote 0
Thanks For your reply

I understand easily thank and please guide me on unique value list please.
 
Upvote 0
=INDEX($A$2:$A$16,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$16),0))

Formula is not working please check and confirm.
 
Upvote 0
Perhaps you did not apply control+shift+enter to the suggested formula.

Another way would be the following.


Book1
ABCDE
1Invioce No.Total11
21176090.03Invioce No.Sum Total
31110577.6311105139.29
41117374.9211173147.8
51187183.2911306123.9
61133496.3411334192.06
71190861.271146886.76
81196167.381176090.03
91110561.661176669.41
101133495.721187183.29
111176669.411190568.37
121146886.761190861.27
131117372.881196167.38
141130669.9
151190568.37
161130654
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))
 
Upvote 0
Perhaps you did not apply control+shift+enter to the suggested formula.

Another way would be the following.

ABCDE
Invioce No.Total
Invioce No.Sum Total

<COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]11760[/TD]
[TD="align: right"]90.03[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]11105[/TD]
[TD="align: right"]77.63[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11105[/TD]
[TD="align: right"]139.29[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]11173[/TD]
[TD="align: right"]74.92[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11173[/TD]
[TD="align: right"]147.8[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]11871[/TD]
[TD="align: right"]83.29[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11306[/TD]
[TD="align: right"]123.9[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]11334[/TD]
[TD="align: right"]96.34[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11334[/TD]
[TD="align: right"]192.06[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]11908[/TD]
[TD="align: right"]61.27[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11468[/TD]
[TD="align: right"]86.76[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]11961[/TD]
[TD="align: right"]67.38[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11760[/TD]
[TD="align: right"]90.03[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]11105[/TD]
[TD="align: right"]61.66[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11766[/TD]
[TD="align: right"]69.41[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]11334[/TD]
[TD="align: right"]95.72[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11871[/TD]
[TD="align: right"]83.29[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]11766[/TD]
[TD="align: right"]69.41[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11905[/TD]
[TD="align: right"]68.37[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11468[/TD]
[TD="align: right"]86.76[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11908[/TD]
[TD="align: right"]61.27[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]11173[/TD]
[TD="align: right"]72.88[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11961[/TD]
[TD="align: right"]67.38[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]11306[/TD]
[TD="align: right"]69.9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]11905[/TD]
[TD="align: right"]68.37[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]11306[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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:confused:
 
Upvote 0
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:confused:


Book1
ABCDE
1Invioce No.Total4
21176090.03Invioce No.Sum Total
31110577.6311105139.29
41117374.9211173147.8
51187183.2911334192.06
61133496.3411306123.9
71190861.27
81196167.38
91110561.66
101133495.72
111176669.41
121146886.76
131117372.88
141130669.9
151190568.37
161130654
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))
 
Upvote 0
=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:cool:
 
Upvote 0

Similar threads

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top