Find duplicate rows in range and sum values in corresponding column

philip121 · Feb 28, 2021

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,

Fluff · Feb 28, 2021

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 · Feb 28, 2021

Yes, columns from A to E must match in order to be considered duplicate.

TEST_Duplicate_Rows.xls

A

B

C

D

E

F

1

SKU

Aisle

Bay

Shelf

Bin

On hand

2

BEBGS00002-5/6

10

4

1

0

5

3

BEBGS00002-6X

10

4

1

0

2

4

BEBGS00006-5/6

10

4

1

0

2

5

BEBGS00006-5/6

10

2

0

4

6

BEBGS00006-7

10

4

1

0

4

7

BEBGS00006-7

10

2

0

5

8

BEBH010

10

3

1

0

16

9

BEBPT00008-7

10

4

1

0

2

10

BEBPT00009-4

10

4

2

0

2

11

BEBPT00009-4

10

2

0

40

12

BEBPT00009-4

10

4

2

0

6

13

BEBPT00009-4

10

4

1

0

4

14

BEBPT00009-5/6

10

2

0

3

15

BEBPT00009-5/6

10

4

1

0

4

16

BEBPT00009-5/6

10

2

0

12

17

BEBPT00009-5/6

10

4

1

0

3

18

BEBPT00009-7

10

2

0

31

19

BEBPT00009-7

10

4

1

0

3

20

BEBPT00009-7

10

4

1

0

3

21

BEBPT00010-4

10

4

1

0

6

22

BEBPT00010-4

10

4

1

0

2

23

BEBPT00010-4

10

4

2

0

57

24

BEBPT00010-5/6

10

4

1

0

6

25

BEBPT00010-5/6

10

4

1

0

2

26

BEBPT00010-5/6

10

4

2

0

53

27

BEBPT00010-7

10

4

1

0

6

28

BEBPT00010-7

10

4

2

0

61

29

BEBTS00003-01

10

4

2

0

36

30

BEBTS00003-01

10

4

1

0

6

31

BEBTS00003-02

10

4

2

0

58

32

BEBTS00003-02

10

4

1

0

5

33

BEBTS00003-03

10

4

1

0

5

34

BEBTS00003-03

10

4

2

0

11

35

BEBTS00009-4

10

4

1

0

2

36

BEBTS00009-7

10

4

1

0

4

37

BEBTS00011-4

10

4

2

0

96

38

BEBTS00011-4

10

4

1

0

8

39

BEBTS00011-5/6

10

4

1

0

7

40

BEBTS00011-5/6

10

4

2

0

69

41

BEBTS00011-7

10

4

1

0

2

42

BEBTS00011-7

10

4

1

0

5

43

BEBTS00011-7

10

4

2

0

112

44

BEBTS00012-4

10

4

2

0

115

45

BEBTS00012-4

10

4

1

0

5

46

BEBTS00012-5/6

10

4

2

0

76

47

BEBTS00012-5/6

10

4

1

0

10

48

BEBTS00012-5/6

10

4

1

0

1

49

BEBTS00012-7

10

4

2

0

118

50

BEBTS00012-7

10

4

1

0

11

51

BEBTS00013-4

10

4

2

0

88

52

BEBTS00013-4

10

4

1

0

3

53

BEBTS00013-5/6

10

4

2

0

62

54

BEBTS00013-7

10

4

2

0

93

55

BEBTS00013-7

10

4

1

0

4

56

BEBTS00014-4

10

4

2

0

44

57

BEBTS00014-4

10

4

1

0

4

58

BEBTS00014-5/6

10

4

2

0

57

59

BEBTS00014-7

10

4

2

0

105

60

BEBTS00014-7

10

4

1

0

6

61

BEBUS00001-5/6

10

6

1

0

3

62

BEBUS00001-7

10

6

1

0

2

63

BEGS001-6X

10

4

1

0

3

64

BEGS001-6X

10

4

1

0

1

65

BEIGS00030-04

10

2

1

0

1

66

BEIGS00030-05

10

2

1

0

4

67

BEIHA00003

10

3

2

0

2

68

BEIHA00003

10

3

2

0

1

69

BEIHA00006

10

3

0

4

70

BEIHA00006

10

3

0

1

71

BEIHA00006

10

3

0

14

72

BEIHA00006

10

3

0

105

73

BEIHA00008

10

3

1

0

6

74

BEIHA00009

10

3

1

0

1

75

BEIO00029-0-3M

10

2

1

0

1

76

BEIO00029-12M

10

2

1

0

1

77

BEIO00031-0-3M

10

2

1

0

1

78

BEIO00032-18M

10

2

1

0

1

79

BEIO00032-18M

10

2

1

0

2

80

BEIO00032-24M

10

2

1

0

2

81

BEIO00033-0-3M

10

2

1

0

1

82

BEIO00033-3-6M

10

2

1

0

3

83

BEIT00002-03

10

2

1

0

2

84

BEIT00003-03

10

2

1

0

3

85

BEIT00003-03

10

2

1

0

2

86

BEIT00003-03

10

2

1

0

9

87

BEIT00003-04

10

2

1

0

4

88

BEIT00003-05

10

2

1

0

2

89

BEIT00004 -12 months

10

2

1

0

10

90

BEIT00004- 18 months

10

2

1

0

1

91

BEIT00004- 18 months

10

2

1

0

7

92

BEIT00004- 24 months

10

2

1

0

3

93

BEIT00008-12M

10

2

1

0

4

94

BEIT00008-18M

10

2

1

0

3

95

BEIT00008-24M

10

2

1

0

1

96

BEIT00008-24M

10

2

1

0

3

97

BEIT00010-12M

10

2

1

0

4

98

BEIT00010-12M

10

2

1

0

6

99

BEIT00010-12M

10

2

0

4

100

BEIT00010-18M

10

2

1

0

3

101

BEIT00010-18M

10

2

1

0

1

102

BEIT00010-18M

10

2

0

6

103

BEMHA00006

10

1

2

0

4

104

BEMHA00006

10

1

2

0

1

Inventory Detail export 2021-02

Fluff · Feb 28, 2021

Thanks for that, how about

+Fluff 1.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

1

SKU

Aisle

Bay

Shelf

Bin

On hand

2

BEBGS00002-5/6

10

4

1

0

5

BEBGS00002-5/6

10

4

1

0

5

3

BEBGS00002-6X

10

4

1

0

2

BEBGS00002-6X

10

4

1

0

2

4

BEBGS00006-5/6

10

4

1

0

2

BEBGS00006-5/6

10

4

1

0

2

5

BEBGS00006-5/6

10

2

0

4

BEBGS00006-5/6

10

2

0

4

6

BEBGS00006-7

10

4

1

0

4

BEBGS00006-7

10

4

1

0

4

7

BEBGS00006-7

10

2

0

5

BEBGS00006-7

10

2

0

5

8

BEBH010

10

3

1

0

16

BEBH010

10

3

1

0

16

9

BEBPT00008-7

10

4

1

0

2

BEBPT00008-7

10

4

1

0

2

10

BEBPT00009-4

10

4

2

0

2

BEBPT00009-4

10

4

2

0

8

11

BEBPT00009-4

10

2

0

40

BEBPT00009-4

10

2

0

40

12

BEBPT00009-4

10

4

2

0

6

BEBPT00009-4

10

4

1

0

4

13

BEBPT00009-4

10

4

1

0

4

BEBPT00009-5/6

10

2

0

15

14

BEBPT00009-5/6

10

2

0

3

BEBPT00009-5/6

10

4

1

0

7

15

BEBPT00009-5/6

10

4

1

0

4

BEBPT00009-7

10

2

0

31

16

BEBPT00009-5/6

10

2

0

12

BEBPT00009-7

10

4

1

0

6

17

BEBPT00009-5/6

10

4

1

0

3

BEBPT00010-4

10

4

1

0

8

18

BEBPT00009-7

10

2

0

31

BEBPT00010-4

10

4

2

0

57

19

BEBPT00009-7

10

4

1

0

3

BEBPT00010-5/6

10

4

1

0

8

20

BEBPT00009-7

10

4

1

0

3

BEBPT00010-5/6

10

4

2

0

53

21

BEBPT00010-4

10

4

1

0

6

BEBPT00010-7

10

4

1

0

6

22

BEBPT00010-4

10

4

1

0

2

BEBPT00010-7

10

4

2

0

61

23

BEBPT00010-4

10

4

2

0

57

BEBTS00003-01

10

4

2

0

36

24

BEBPT00010-5/6

10

4

1

0

6

BEBTS00003-01

10

4

1

0

6

25

BEBPT00010-5/6

10

4

1

0

2

BEBTS00003-02

10

4

2

0

58

26

BEBPT00010-5/6

10

4

2

0

53

BEBTS00003-02

10

4

1

0

5

27

BEBPT00010-7

10

4

1

0

6

BEBTS00003-03

10

4

1

0

5

28

BEBPT00010-7

10

4

2

0

61

BEBTS00003-03

10

4

2

0

11

29

BEBTS00003-01

10

4

2

0

36

BEBTS00009-4

10

4

1

0

2

30

BEBTS00003-01

10

4

1

0

6

BEBTS00009-7

10

4

1

0

4

31

BEBTS00003-02

10

4

2

0

58

BEBTS00011-4

10

4

2

0

96

32

BEBTS00003-02

10

4

1

0

5

BEBTS00011-4

10

4

1

0

8

33

BEBTS00003-03

10

4

1

0

5

BEBTS00011-5/6

10

4

1

0

7

34

BEBTS00003-03

10

4

2

0

11

BEBTS00011-5/6

10

4

2

0

69

35

BEBTS00009-4

10

4

1

0

2

BEBTS00011-7

10

4

1

0

7

36

BEBTS00009-7

10

4

1

0

4

BEBTS00011-7

10

4

2

0

112

37

BEBTS00011-4

10

4

2

0

96

BEBTS00012-4

10

4

2

0

115

38

BEBTS00011-4

10

4

1

0

8

BEBTS00012-4

10

4

1

0

5

39

BEBTS00011-5/6

10

4

1

0

7

BEBTS00012-5/6

10

4

2

0

76

40

BEBTS00011-5/6

10

4

2

0

69

BEBTS00012-5/6

10

4

1

0

11

41

BEBTS00011-7

10

4

1

0

2

BEBTS00012-7

10

4

2

0

118

42

BEBTS00011-7

10

4

1

0

5

BEBTS00012-7

10

4

1

0

11

43

BEBTS00011-7

10

4

2

0

112

BEBTS00013-4

10

4

2

0

88

44

BEBTS00012-4

10

4

2

0

115

BEBTS00013-4

10

4

1

0

3

45

BEBTS00012-4

10

4

1

0

5

BEBTS00013-5/6

10

4

2

0

62

46

BEBTS00012-5/6

10

4

2

0

76

BEBTS00013-7

10

4

2

0

93

47

BEBTS00012-5/6

10

4

1

0

10

BEBTS00013-7

10

4

1

0

4

48

BEBTS00012-5/6

10

4

1

0

1

BEBTS00014-4

10

4

2

0

44

49

BEBTS00012-7

10

4

2

0

118

BEBTS00014-4

10

4

1

0

4

50

BEBTS00012-7

10

4

1

0

11

BEBTS00014-5/6

10

4

2

0

57

51

BEBTS00013-4

10

4

2

0

88

BEBTS00014-7

10

4

2

0

105

52

BEBTS00013-4

10

4

1

0

3

BEBTS00014-7

10

4

1

0

6

53

BEBTS00013-5/6

10

4

2

0

62

BEBUS00001-5/6

10

6

1

0

3

54

BEBTS00013-7

10

4

2

0

93

BEBUS00001-7

10

6

1

0

2

55

BEBTS00013-7

10

4

1

0

4

BEGS001-6X

10

4

1

0

4

56

BEBTS00014-4

10

4

2

0

44

BEIGS00030-04

10

2

1

0

1

57

BEBTS00014-4

10

4

1

0

4

BEIGS00030-05

10

2

1

0

4

58

BEBTS00014-5/6

10

4

2

0

57

BEIHA00003

10

3

2

0

3

59

BEBTS00014-7

10

4

2

0

105

BEIHA00006

10

3

0

124

60

BEBTS00014-7

10

4

1

0

6

BEIHA00008

10

3

1

0

6

61

BEBUS00001-5/6

10

6

1

0

3

BEIHA00009

10

3

1

0

1

62

BEBUS00001-7

10

6

1

0

2

BEIO00029-0-3M

10

2

1

0

1

63

BEGS001-6X

10

4

1

0

3

BEIO00029-12M

10

2

1

0

1

64

BEGS001-6X

10

4

1

0

1

BEIO00031-0-3M

10

2

1

0

1

65

BEIGS00030-04

10

2

1

0

1

BEIO00032-18M

10

2

1

0

3

66

BEIGS00030-05

10

2

1

0

4

BEIO00032-24M

10

2

1

0

2

67

BEIHA00003

10

3

2

0

2

BEIO00033-0-3M

10

2

1

0

1

68

BEIHA00003

10

3

2

0

1

BEIO00033-3-6M

10

2

1

0

3

69

BEIHA00006

10

3

0

4

BEIT00002-03

10

2

1

0

2

70

BEIHA00006

10

3

0

1

BEIT00003-03

10

2

1

0

14

71

BEIHA00006

10

3

0

14

BEIT00003-04

10

2

1

0

4

72

BEIHA00006

10

3

0

105

BEIT00003-05

10

2

1

0

2

73

BEIHA00008

10

3

1

0

6

BEIT00004 -12 months

10

2

1

0

10

74

BEIHA00009

10

3

1

0

1

BEIT00004- 18 months

10

2

1

0

8

75

BEIO00029-0-3M

10

2

1

0

1

BEIT00004- 24 months

10

2

1

0

3

76

BEIO00029-12M

10

2

1

0

1

BEIT00008-12M

10

2

1

0

4

77

BEIO00031-0-3M

10

2

1

0

1

BEIT00008-18M

10

2

1

0

3

78

BEIO00032-18M

10

2

1

0

1

BEIT00008-24M

10

2

1

0

4

79

BEIO00032-18M

10

2

1

0

2

BEIT00010-12M

10

2

1

0

10

80

BEIO00032-24M

10

2

1

0

2

BEIT00010-12M

10

2

0

4

81

BEIO00033-0-3M

10

2

1

0

1

BEIT00010-18M

10

2

1

0

4

82

BEIO00033-3-6M

10

2

1

0

3

BEIT00010-18M

10

2

0

6

83

BEIT00002-03

10

2

1

0

2

BEMHA00006

10

1

2

0

5

84

BEIT00003-03

10

2

1

0

3

85

BEIT00003-03

10

2

1

0

2

86

BEIT00003-03

10

2

1

0

9

87

BEIT00003-04

10

2

1

0

4

88

BEIT00003-05

10

2

1

0

2

89

BEIT00004 -12 months

10

2

1

0

10

90

BEIT00004- 18 months

10

2

1

0

1

91

BEIT00004- 18 months

10

2

1

0

7

92

BEIT00004- 24 months

10

2

1

0

3

93

BEIT00008-12M

10

2

1

0

4

94

BEIT00008-18M

10

2

1

0

3

95

BEIT00008-24M

10

2

1

0

1

96

BEIT00008-24M

10

2

1

0

3

97

BEIT00010-12M

10

2

1

0

4

98

BEIT00010-12M

10

2

1

0

6

99

BEIT00010-12M

10

2

0

4

100

BEIT00010-18M

10

2

1

0

3

101

BEIT00010-18M

10

2

1

0

1

102

BEIT00010-18M

10

2

0

6

103

BEMHA00006

10

1

2

0

4

104

BEMHA00006

10

1

2

0

1

Master

Cell Formulas
Range	Formula
H2:L83	H2	=UNIQUE(A2:E104)
M2:M83	M2	=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.

philip121 · Feb 28, 2021

Perfect! Thank you, I really appreciate your quick response and assistance.

Fluff · Feb 28, 2021

You're welcome & thanks for the feedback.

Find duplicate rows in range and sum values in corresponding column

philip121

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

philip121

New Member

Fluff

MrExcel MVP, Moderator

philip121

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Find duplicate rows in range and sum values in corresponding column

philip121

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

philip121

New Member

Fluff

MrExcel MVP, Moderator

philip121

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock