Find Duplicate with formula and make idividual list

Navi_G

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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
[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
Aladin Akyurek said:
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))
Click to expand...

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
Navi_G said:
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:
Click to expand...


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
You must log in or register to reply here.

Similar threads

Navi_G
  • Locked
  • Question Question
Find Unique list updatable
Replies
2
Views
682
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

Share this page

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