Find duplicate rows in range and sum values in corresponding column

philip121

New Member
Joined
May 22, 2014
Messages
13
Office Version
  1. 365
Platform
  1. MacOS
Hello,

How can I find duplicate rows from columns A through E. Then sum the values in column F of these duplicates?
Additionally, If the row is unique just copy over the entire row.

In this example, row 2 column A through E is unique. So the entire row A through F is copied over to the right hand side.
Rows 3 through, 7 columns A to E are duplicates. So, sum the values in column F and place the result in the next row on the right hand side.
Screen Shot 2021-02-28 at 11.59.14 AM.png


Any assistance would be greatly appreciated.

Thanks,
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also do all columns fro A to E have to match to be considered a duplicate, or just col A?
 

philip121

New Member
Joined
May 22, 2014
Messages
13
Office Version
  1. 365
Platform
  1. MacOS
Yes, columns from A to E must match in order to be considered duplicate.

TEST_Duplicate_Rows.xls
ABCDEF
1SKUAisleBayShelfBinOn hand
2BEBGS00002-5/6104105
3BEBGS00002-6X104102
4BEBGS00006-5/6104102
5BEBGS00006-5/6102204
6BEBGS00006-7104104
7BEBGS00006-7102205
8BEBH0101031016
9BEBPT00008-7104102
10BEBPT00009-4104202
11BEBPT00009-41022040
12BEBPT00009-4104206
13BEBPT00009-4104104
14BEBPT00009-5/6102203
15BEBPT00009-5/6104104
16BEBPT00009-5/61022012
17BEBPT00009-5/6104103
18BEBPT00009-71022031
19BEBPT00009-7104103
20BEBPT00009-7104103
21BEBPT00010-4104106
22BEBPT00010-4104102
23BEBPT00010-41042057
24BEBPT00010-5/6104106
25BEBPT00010-5/6104102
26BEBPT00010-5/61042053
27BEBPT00010-7104106
28BEBPT00010-71042061
29BEBTS00003-011042036
30BEBTS00003-01104106
31BEBTS00003-021042058
32BEBTS00003-02104105
33BEBTS00003-03104105
34BEBTS00003-031042011
35BEBTS00009-4104102
36BEBTS00009-7104104
37BEBTS00011-41042096
38BEBTS00011-4104108
39BEBTS00011-5/6104107
40BEBTS00011-5/61042069
41BEBTS00011-7104102
42BEBTS00011-7104105
43BEBTS00011-710420112
44BEBTS00012-410420115
45BEBTS00012-4104105
46BEBTS00012-5/61042076
47BEBTS00012-5/61041010
48BEBTS00012-5/6104101
49BEBTS00012-710420118
50BEBTS00012-71041011
51BEBTS00013-41042088
52BEBTS00013-4104103
53BEBTS00013-5/61042062
54BEBTS00013-71042093
55BEBTS00013-7104104
56BEBTS00014-41042044
57BEBTS00014-4104104
58BEBTS00014-5/61042057
59BEBTS00014-710420105
60BEBTS00014-7104106
61BEBUS00001-5/6106103
62BEBUS00001-7106102
63BEGS001-6X104103
64BEGS001-6X104101
65BEIGS00030-04102101
66BEIGS00030-05102104
67BEIHA00003103202
68BEIHA00003103201
69BEIHA00006103304
70BEIHA00006103301
71BEIHA000061033014
72BEIHA0000610330105
73BEIHA00008103106
74BEIHA00009103101
75BEIO00029-0-3M102101
76BEIO00029-12M102101
77BEIO00031-0-3M102101
78BEIO00032-18M102101
79BEIO00032-18M102102
80BEIO00032-24M102102
81BEIO00033-0-3M102101
82BEIO00033-3-6M102103
83BEIT00002-03102102
84BEIT00003-03102103
85BEIT00003-03102102
86BEIT00003-03102109
87BEIT00003-04102104
88BEIT00003-05102102
89BEIT00004 -12 months1021010
90BEIT00004- 18 months102101
91BEIT00004- 18 months102107
92BEIT00004- 24 months102103
93BEIT00008-12M102104
94BEIT00008-18M102103
95BEIT00008-24M102101
96BEIT00008-24M102103
97BEIT00010-12M102104
98BEIT00010-12M102106
99BEIT00010-12M102204
100BEIT00010-18M102103
101BEIT00010-18M102101
102BEIT00010-18M102206
103BEMHA00006101204
104BEMHA00006101201
Inventory Detail export 2021-02
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
+Fluff 1.xlsm
ABCDEFGHIJKLM
1SKUAisleBayShelfBinOn hand
2BEBGS00002-5/6104105BEBGS00002-5/6104105
3BEBGS00002-6X104102BEBGS00002-6X104102
4BEBGS00006-5/6104102BEBGS00006-5/6104102
5BEBGS00006-5/6102204BEBGS00006-5/6102204
6BEBGS00006-7104104BEBGS00006-7104104
7BEBGS00006-7102205BEBGS00006-7102205
8BEBH0101031016BEBH0101031016
9BEBPT00008-7104102BEBPT00008-7104102
10BEBPT00009-4104202BEBPT00009-4104208
11BEBPT00009-41022040BEBPT00009-41022040
12BEBPT00009-4104206BEBPT00009-4104104
13BEBPT00009-4104104BEBPT00009-5/61022015
14BEBPT00009-5/6102203BEBPT00009-5/6104107
15BEBPT00009-5/6104104BEBPT00009-71022031
16BEBPT00009-5/61022012BEBPT00009-7104106
17BEBPT00009-5/6104103BEBPT00010-4104108
18BEBPT00009-71022031BEBPT00010-41042057
19BEBPT00009-7104103BEBPT00010-5/6104108
20BEBPT00009-7104103BEBPT00010-5/61042053
21BEBPT00010-4104106BEBPT00010-7104106
22BEBPT00010-4104102BEBPT00010-71042061
23BEBPT00010-41042057BEBTS00003-011042036
24BEBPT00010-5/6104106BEBTS00003-01104106
25BEBPT00010-5/6104102BEBTS00003-021042058
26BEBPT00010-5/61042053BEBTS00003-02104105
27BEBPT00010-7104106BEBTS00003-03104105
28BEBPT00010-71042061BEBTS00003-031042011
29BEBTS00003-011042036BEBTS00009-4104102
30BEBTS00003-01104106BEBTS00009-7104104
31BEBTS00003-021042058BEBTS00011-41042096
32BEBTS00003-02104105BEBTS00011-4104108
33BEBTS00003-03104105BEBTS00011-5/6104107
34BEBTS00003-031042011BEBTS00011-5/61042069
35BEBTS00009-4104102BEBTS00011-7104107
36BEBTS00009-7104104BEBTS00011-710420112
37BEBTS00011-41042096BEBTS00012-410420115
38BEBTS00011-4104108BEBTS00012-4104105
39BEBTS00011-5/6104107BEBTS00012-5/61042076
40BEBTS00011-5/61042069BEBTS00012-5/61041011
41BEBTS00011-7104102BEBTS00012-710420118
42BEBTS00011-7104105BEBTS00012-71041011
43BEBTS00011-710420112BEBTS00013-41042088
44BEBTS00012-410420115BEBTS00013-4104103
45BEBTS00012-4104105BEBTS00013-5/61042062
46BEBTS00012-5/61042076BEBTS00013-71042093
47BEBTS00012-5/61041010BEBTS00013-7104104
48BEBTS00012-5/6104101BEBTS00014-41042044
49BEBTS00012-710420118BEBTS00014-4104104
50BEBTS00012-71041011BEBTS00014-5/61042057
51BEBTS00013-41042088BEBTS00014-710420105
52BEBTS00013-4104103BEBTS00014-7104106
53BEBTS00013-5/61042062BEBUS00001-5/6106103
54BEBTS00013-71042093BEBUS00001-7106102
55BEBTS00013-7104104BEGS001-6X104104
56BEBTS00014-41042044BEIGS00030-04102101
57BEBTS00014-4104104BEIGS00030-05102104
58BEBTS00014-5/61042057BEIHA00003103203
59BEBTS00014-710420105BEIHA0000610330124
60BEBTS00014-7104106BEIHA00008103106
61BEBUS00001-5/6106103BEIHA00009103101
62BEBUS00001-7106102BEIO00029-0-3M102101
63BEGS001-6X104103BEIO00029-12M102101
64BEGS001-6X104101BEIO00031-0-3M102101
65BEIGS00030-04102101BEIO00032-18M102103
66BEIGS00030-05102104BEIO00032-24M102102
67BEIHA00003103202BEIO00033-0-3M102101
68BEIHA00003103201BEIO00033-3-6M102103
69BEIHA00006103304BEIT00002-03102102
70BEIHA00006103301BEIT00003-031021014
71BEIHA000061033014BEIT00003-04102104
72BEIHA0000610330105BEIT00003-05102102
73BEIHA00008103106BEIT00004 -12 months1021010
74BEIHA00009103101BEIT00004- 18 months102108
75BEIO00029-0-3M102101BEIT00004- 24 months102103
76BEIO00029-12M102101BEIT00008-12M102104
77BEIO00031-0-3M102101BEIT00008-18M102103
78BEIO00032-18M102101BEIT00008-24M102104
79BEIO00032-18M102102BEIT00010-12M1021010
80BEIO00032-24M102102BEIT00010-12M102204
81BEIO00033-0-3M102101BEIT00010-18M102104
82BEIO00033-3-6M102103BEIT00010-18M102206
83BEIT00002-03102102BEMHA00006101205
84BEIT00003-03102103
85BEIT00003-03102102
86BEIT00003-03102109
87BEIT00003-04102104
88BEIT00003-05102102
89BEIT00004 -12 months1021010
90BEIT00004- 18 months102101
91BEIT00004- 18 months102107
92BEIT00004- 24 months102103
93BEIT00008-12M102104
94BEIT00008-18M102103
95BEIT00008-24M102101
96BEIT00008-24M102103
97BEIT00010-12M102104
98BEIT00010-12M102106
99BEIT00010-12M102204
100BEIT00010-18M102103
101BEIT00010-18M102101
102BEIT00010-18M102206
103BEMHA00006101204
104BEMHA00006101201
Master
Cell Formulas
RangeFormula
H2:L83H2=UNIQUE(A2:E104)
M2:M83M2=SUMIFS($F$2:$F$104,$A$2:$A$104,INDEX(H2#,,1),$B$2:$B$104,INDEX(H2#,,2),$C$2:$C$104,INDEX(H2#,,3),$D$2:$D$104,INDEX(H2#,,4),$E$2:$E$104,INDEX(H2#,,5))
Dynamic array formulas.
 
Solution

philip121

New Member
Joined
May 22, 2014
Messages
13
Office Version
  1. 365
Platform
  1. MacOS
Perfect! Thank you, I really appreciate your quick response and assistance.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,382
Messages
5,635,937
Members
416,889
Latest member
dhegs

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
Top