[SOLVED] Matching a row where two cols have multiple, repetitive values

Matching a row where two cols have multiple, repetitive values

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.

Solution

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.