I am making an inventory system in Excel and cannot figure out how to get all cell values that are duplicates. So far I have this formula to find how many duplicates. I need to find the cell row of the duplicates so I can get the values and preform other calculations.

The cells in B column contain text J column contains only integers

=IF(COUNTIF($B$3:$B$99999,B11) > 1, "more than one duplicate", "no duplicate")

What I would like the formula to do:

=IF(COUNTIF($B$3:$B$99999,B11) > 1, ("J"   DuplicateCellRow1)   ("J"   DuplicateCellRow2), "no duplicate")

Edit: Here is some sample data The Yellow is duplicate products with different UPC codes. This happens because we order from different suppliers and none of them use similar codes. I basically want to add up all the similars from "QTY" into the "Combined QTY" column.

e_conomics suggestion worked, however the second duplicate is also printing out a number. Is there a way to prevent that or to filter it out? This is a master file so I will be creating nicer looking reports in a different sheet

CodePudding user response：

Made a slight adjustment to the formula.The cell referencing was incorrect.

=IF(COUNTIF($B$3:$B$99999,B3) > 1, SUMIFS($J$3:$J$99999,$B$3:$B$99999,B3), "no duplicate")

Paste this into K3 and drag down.