Counting duplicate numbers in various rows on a sheet

[LarryinWaterloo]

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!

(Edit: activated screenshot -AK)

[SM_Riga]

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?

[LarryinWaterloo]

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 and 10 contain groupings of 3 numbers and should have been highlighted. Again, thanks for the help! Larry.

[KamilLanda]

I can imagine it is possible to do it with a macro, but if there are tens or hundreds of thousands of lines in the sheet, then the macro in Basic will be slow. It would be better to take data from the sheet with the method GetDataArray and use some Python functions for array operations to find duplicates. But unfortunately I don’t know Python well.