TRUE / FALSE result - ROW Duplication / Duplication in ROW

Copper Contributor

Hoping it doesn't seem too much of an ask, but I have two (2) requests for the one data set.
The seven (7) columns has multiple ROWs with numbers in them that can't be sorted
The numbers may vary in the ROWs
The numbers may be duplicated in the ROWs, or in the ROW data


Return1: Is a TRUE / FALSE result for a comparison for duplication of the selected ROW (Columns header: Example 1-7) to previous ROWs
1 = No Duplicate ROW
0 = Duplicate ROW

Would like to count (i.e. = 1) the ROW if it isn't a duplicate (other COUNT / SUM etc. formula can be applied later to aggregate data etc. - not require at this point in time)
Duplication is if the selected ROW and previous ROW/s have the same numbers even if they're in different COLUMNs

Tried various formulas, but couldn't get them to fit. The INDEX-MATCH seemed like a win, but came unstuck with the criteria and search range, since it starts to get messy with the seven (7) columns, and the criteria is the entire ROW, not just one CELL, then there's the issue with MATCH and how it only searches in one (1) ROW / COLUMN - Plus I'm not great with ARRAY ({}) type formulas, I can copy & use VBA pretty well, but illiterate when it comes to writing it..


Return2: Is a TRUE / FALSE result for duplication in the ROW data / in the ROW itself
1 = No Duplicate in ROW
0 = Duplicate in ROW

Know how to work Conditional Formatting to locate duplication for individual ROWs; unsure how to enlarge for a wider scale (thousands of ROWs)

1 Reply


For your return 1, you need an auxiliary column in my solution. I used the following formula to assess whether the same numbers were used:


However, there are probably other functions from the statistics section, e.g.:


I'm not so sure about that, though.

Return 1:


Return 2:


The whole thing is also in my attached document. I hope this helps you.