Counting duplicate numbers in various rows on a sheet

asked 2019-03-09 21:05:01 +0100

Larry in Waterloo gravatar image

updated 2019-03-09 21:07:03 +0100

Hello. This question has been answered in various ways, but not in ways that suit my application. An example screen shot is attached. I want to count duplicate occurrences of any set of numbers row-by-row on a sheet. The formula in a separate column ("Duplicates") is highlighted if it exceeds three numbers duplicated in any other row, as shown on rows 4 and 11. The raw data columns/rows don't need to be highlighted; I just did that for clarity. I will highlight the duplicates column with a rule; all I need is a formula for the column. Thanks huge for any help provided! C:\fakepath\LibreOffice Question.png

edit retag flag offensive close merge delete


Hi there. I am not sure if I understood your logic. 1. Single cells do not qualify for counting duplicates, correct? Otherwise, cells with 13 would be duplicates. 2. Two or more adjacent cells, if all their values are found in any other row, qualify for counting duplicates, correct? This suggestion made based on {29|30|33|40} from rows 4 and 11, and {34|38|41} rows 10, 11. Then why {31|34|38} from row 3 is not duplicate for row 10? Why {29|30} from row 4 is not duplicate for row 6? {31|38} rows 8 and 10 and some more same occurrences?

SM_Riga gravatar imageSM_Riga ( 2019-03-09 22:17:59 +0100 )edit

Thank you for getting back to me, SM_Riga! Sorry, I should have been clearer in my intent. I am not worried about individual cells duplicating other cells. I am looking for duplicates of groupings of cells of one row duplicating groupings of cells in another row. And I am OK with a group of 2 matching a group of 2 in another row, as happens in rows 6 and 9 (20-29) or groupings of 3 matching a grouping of 3, as happens in rows 9 and 10 (34-38-41). For my purposes I want groupings of 4 or more to stand out, as in the case of rows 4 and 11. And the numbers' locations in the groupings doesn't matter (for example see rows 4 and 11, and in rows 9 and 10). All the cells in each row have been sorted in ascending order. You are correct, rows 3 ...(more)

Larry in Waterloo gravatar imageLarry in Waterloo ( 2019-03-09 23:39:00 +0100 )edit