Formula to find a row that uniquely matches multiple criteria, including a regular expression

asked 2018-09-20 20:31:16 +0100

Mossy gravatar image

updated 2018-09-22 15:30:14 +0100

C:\fakepath\MatchCriteria.ods

The following formula counts the number of rows that match certain criteria. Is there a formula that will find the row that matches those same criteria when there is exactly one such row?

=IF(LEN(A2), 
    COUNTIFS(
        $Sheet2.$A:$Sheet2.$A, "=" & A2,
        $Sheet2.$C:$Sheet2.$C, "=" & COUNTIF($A:$A,"=" & A2),
        $Sheet2.$D:$Sheet2.$D, ">" & SUMIF($A:$A,"=" & A2,$B:$B) - 0.5, 
        $Sheet2.$D:$Sheet2.$D, "<" & SUMIF($A:$A,"=" & A2,$B:$B) + 0.5
    )
,"")

I'd also like to be able to replace references to A2 in the formula with a regular expression string, which I can do in the formula above because COUNTIFS, COUNTIF and SUMIF all accept regular expressions.

The rest of this post is context for the above question

I have two spreadsheets (one sheet in each) both with data relating to the same list of people. The data relating to each person can be used to find which person matches which between the two spreadsheets. Only of the spreadsheets has an ID for each person. I need to add the each person's ID to the spreadsheet that doesn't have them.

To do this I've written a macro that

1) Imports both spreadsheets into a new spreadsheet. The first sheet (Sheet1) in the new spreadsheet is the one without IDs, the second sheet (Sheet2) has the IDs

2) Adds a column to Sheet1 ("Match Count") with a formula for each person which counts the number of rows in Sheet2 that the matches the person's name and other criteria (See below)

3) Adds a column to Sheet1 ("ID") with a formula that shows the ID from Sheet2 when the "Match Count" value is 1. In Sheet1 there are one or more rows for each person, grouped together. Sheet1 has an "Amounts" field which has a numeric value in each row.

Sheet2 has one row for each person, and has the columns "Number of Amounts", which corresponds to the number of rows for the corresponding person in Sheet1, and "Total Amount" which corresponds to the sum of the "Amounts" values for the same person in Sheet1. For the purposes of matching people between the two sheets "Total Amount" can be up to 0.5 more or less than the sum of the corresponding "Amount" values in Sheet1. There can be different people with the identical names but they will have different "Amounts".

This is the "Match Count" formula (without the nested SUBSTITUTEs for readability)

A is "Name" in both sheets Sheet2: B = "ID", C = "Number of Amounts", D = "Total Amount"

=IF(LEN(A2), 
    COUNTIFS(
        $Sheet2.$A:$Sheet2.$A, "=" & A2,
        $Sheet2.$C:$Sheet2.$C, "=" & COUNTIF($A:$A,"=" & A2),
        $Sheet2.$D:$Sheet2.$D, ">" & SUMIF($A:$A,"=" & A2,$B:$B) - 0.5, 
        $Sheet2.$D:$Sheet2.$D, "<" & SUMIF($A:$A,"=" & A2,$B:$B) + 0.5
    )
,"")

That works great, but I want is a formula that gets data ... (more)

edit retag flag offensive close merge delete