4

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.

  • 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 Jun 16 '16 at 3:35
  • I've made a couple of edits to hopefully make this more clear, but if more is desired please let me know. – Russ Jun 24 '16 at 16:21
  • 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 Jun 24 '16 at 16:31
  • 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 Jun 27 '16 at 20:21
  • 2
    Have you tried using transpose?, Range1=transpose($b$2:$g$6) then the rest should work without being changed from original answer – bvaughn Aug 8 '16 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.