-1

I'm trying to match two cells in an area that has two columns, each with multiple repetitive values, and simply return something that indicates there is a match row.

I'm doing this in LibreOffice Calc, but I'd like to be able to share it in an Excel spreadsheet if possible.

My spreadsheet search range looks like this:

  |   A    |           B           |   C   |          D            |   
 1| 1782.87|Eva_Estelle            | 496.15|J.B. (LBarneck)        |
 2| 1782.87|Eva_Estelle            | 214.74|Jessica Laity          |
 3| 1782.87|Eva_Estelle            |  57.50|arndtfamily1           |
 4|  905.28|A.N. (robertn)         | 615.29|rochellemallory2005    |
 5|  905.28|A.N. (robertn)         | 367.37|Shenazar James Gill    |
 6|  905.28|A.N. (robertn)         | 366.90|pfitzgerald6           |
 7|  615.29|rochellemallory2005    | 905.28|A.N. (robertn)         |
 8|  615.29|rochellemallory2005    | 367.37|Shenazar James Gill    |
 9|  615.29|rochellemallory2005    | 366.90|pfitzgerald6           |
10|  615.29|rochellemallory2005    | 281.19|John Gill              |
11|  615.29|rochellemallory2005    | 242.96|ANGEL Ballamy          |

My result/query area looks (should look) like this:

  |          A         |     B      |       C       |          D         |
 1|                    |Eva_Estelle |A.N. (robertn) |rochellemallory2005 |
 2|Eva_Estelle         |            |               |                    |
 3|A.N. (robertn)      |            |               |          Y         |
 4|rochellemallory2005 |            |       Y       |                    |

Where "Y" (or something) indicates that there is a row in the B column of the search area that matches query area $A2(A2,A3,A4,..), and where the same row in col D matches query area B$1(B1,C1,D1,..), etc.

The problem is that both cols B and D in the search area contain repetitive data and the search area rows are sorted by the values in cols A then C, descending. Meaning I can't use Lookup functions(?).

Is it possible to do this with a formula in the query area cells, or if not can someone who understands OO or LibreOffice Calc help me with the code I need to create a user defined formula using their version of macro "basic" (so I can hopefully follow what it's doing)? I'll also try to get it if you use BeanShell, JavaScript, or Python, but I'm most familiar with VBasic.

RainMan Jim
  • 1
  • 3
  • 1
    Seems like your result area B1 should be one of `J.B. (LBarneck)`, `Jessica Laity`, `arndtfamily1`. Is that correct? – Ampersand May 12 '17 at 15:26
  • No, B1 is what I need to match to the same row that I need to match (or not) with A2 in the same row (but different col) of the search area. Eventually, I may have J.B. (LBarneck), Jessica Laity, and arndtfamily1 in other columns of the search area, and in that case there should be a Y in the cell of row 2 at those columns. In my example result area, what it should display is correctly shown. Hope that makes sense! – RainMan Jim May 12 '17 at 17:28
  • Basically, I'm looking to create a 'matrix' that shows who in col B of the search area has in common with col D in the search area, and vice-versa. It's also just as important to see who is NOT in common with each other (by a blank cell, or 'N'. But blank would be best, imo).. – RainMan Jim May 12 '17 at 17:34
  • I see I might have screwed up my first reply. In the result area A2 should match with search area col B, and result area B1 match search col D (on the same search area row). If they appear together (same search area row) then indicate 'Y' in cell B2 (the formula cell), but if they don't appear together show no indication (blank) or show 'N'. – RainMan Jim May 12 '17 at 17:49
  • For whoever downvoted this, I'm not sure how to make it more clear, and I've tried all of the permutations I can think of with IF, INDEX, MATCH, HLOOKUP, VLOOKUP, etc. but the problem is confining my search to only col D once I've found a match in col B. Maybe you can suggest the right keywords to search for here? – RainMan Jim May 12 '17 at 20:35
  • Ok, I saw where my result example table may have confused you and I corrected it. Based on the data in the 1st table example, I want to create a 2nd table as shown in the example. – RainMan Jim May 13 '17 at 00:42

1 Answers1

0

Insert a header row of labels (I used A>D), select Columns A:D, Insert > Pivot Table..., OK, drag B to Row Fields:, D to Column Fields:, and D to Data Fields:. Change Sum - D to Count, OK, OK.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    To quote Steve Martin from the movie "The Jerk" ... "Ahh. It's a **Pivot Table** scheme!". I suppose I need to understand them better, and put more time into ensuring my example tables are correct, and far less time trying to describe my problem! THANK You. – RainMan Jim May 13 '17 at 05:41