Excel- How do I find common text values from multiple (>2) rows?

4

2

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.

Russ

Posted 2016-06-15T22:02:45.410

Reputation: 41

great you've already tried! please also add what's the issue with your final formula (gives error message / wrong result), also please post some sample data. – Máté Juhász – 2016-06-16T03:35:32.457

I've made a couple of edits to hopefully make this more clear, but if more is desired please let me know. – Russ – 2016-06-24T16:21:09.933

I can't check on a PC now, but probably you need to change the order of parameters of MMULT function. – Máté Juhász – 2016-06-24T16:31:56.030

I've been stepping through and breaking down each part, and noticed an oddity. If I simply test the Arry3 by entering it into a cell, i get a #VALUE message. If I take each array portion of the MMULT formula, enter them separately into cells as formulas (say cell B19 and C19 for testing), then change the Arry3 formula to =MMULT(B19,C19) I no longer get the error. This still does not make it work, but it seems odd and perhaps is important. – Russ – 2016-06-27T20:21:02.127

2Have you tried using transpose?, Range1=transpose($b$2:$g$6) then the rest should work without being changed from original answer – bvaughn – 2016-08-08T14:08:42.370

Write an UDF instead. It will be much easier to understand and maintain. – airstrike – 2016-10-12T15:14:08.860

@Russ, my experience says that your data structure does't supports Excel Formula to extract Duplicates in Rows,, since both supporting values Source & C ordinates are unique, therefore Excel fails to get the proper match, instead I can suggest you method to Highlight duplicates i Row! – Rajesh S – 2019-06-14T10:34:18.280

No answers