This is essentially a variant of the question asked here: Excel- How do I find common text values from multiple (>2) columns?

In this case, however, instead of multiple columns of data I need to search multiple rows. So, each row may have up to 17 columns of non-repeated data, and I want to find a match or matches between rows. Here is a small sample of how the data might look (it is a list of possible coordinates for a site gathered from multiple sources, the intent being to pinpoint a unique location):

Source Coord1 Coord2 Coord3 Coord4 Coord5 Coord6 Source1 (4,0) (5,0) Source2 (3,0) (4,0) (5,0) (3,1) (4,1) (5,1) Source3 (4,0) (5,1) (5,0) Source4 (3,0) (4,0) (5,0) (3,1) (3,2) Source5 (2,3) (3,2) (4,1) (4,2) (5,0)

In the referenced thread, user XOR-LX provided a very useful method for column-wise comparison. Indeed, if I transpose the above data his method works fine, returning (5,0) as the unique location in the first output cell, but I have been unable to modify it for row-wise use, as my data is arranged. So far I have attempted to modify it in the following way, with no luck:

- Range1 =
`$B$2:$G$6`

- Arry1 =
`ROW(Range1)-MIN(ROW(Range1))`

- Arry2 =
`COLUMN(INDEX(Range1,1,))-MIN(COLUMN(INDEX(Range1,1,)))+1`

- Arry3 =
`MMULT(0+COUNTIF(OFFSET(INDEX(Range1,1,),Arry1,,,),INDEX(Range1,1,))>0),COLUMN(INDIRECT(ROWS(Range1)&":1"))^0)`

With the final input being:

`=IFERROR(INDEX(INDEX(Range1,1,),SMALL(IF(FREQUENCY(IF(INDEX(Range1,1,)<>"",IF(Arry3=ROWS(Range1),MATCH(INDEX(Range1,1,),INDEX(Range1,1,),0))),Arry2),Arry2),COLUMNS(A:$A))),"")`

Using the above modification, I simply get no output (nor error messages), just a blank cell. I essentially tried to just "reverse" his method by swapping ROW for COLUMN and so forth, but I suspect the solution may be slightly more complex, especially given my weakness with excel matrix functions.

Any help is greatly appreciated.

`MMULT`

function. – Máté Juhász Jun 24 '16 at 16:31