Challenging problem

[carddard]

Hi guys,

I have to data sets placed alongside each other.
One spans from columns A-AL.
The other from columns CB-DO.
Both data sets have different number of rows.
Both data sets can be matched with their security number. (ie. Columns A & CB respectively)

I need to do the following in column AL of data set A-AL:

1) If security number is found in set A-AL and not found in CB-DO, display "Not Found"

2) Otherwise If H = CI, & I = CJ, display "OK".

3) Otherwise display "CHECK".

I have attached a sample file HERE.

Please help, somebody!

Thanks!

 

[Aladin Akyurek]

Try...

=IF(ISNA(MATCH(A2,$CB$2:$CB$7,0)),
    "Not Found",
   IF(INDEX($CI$2:$CI$7,MATCH(A2,$CB$2:$CB$7,0))=H2,
     IF(INDEX($CJ$2:$CJ$7,MATCH(A2,$CB$2:$CB$7,0))=I2,
       "OK",
       "CHECK"),
     "CHECK"))

Note. You can avoid repeating the MATCH bit by either having it in a cell of its own, say, in J2:

J2:

=MATCH(A2,$CB$2:$CB$7,0)

which allows you to re-write the main formula as:

=IF(ISNA(J2),
    "Not Found",
   IF(INDEX($CI$2:$CI$7,J2)=H2,
     IF(INDEX($CJ$2:$CJ$7,J2)=I2,
       "OK",
       "CHECK"),
     "CHECK"))

or using the SETV/GETV pair from the morefunc.xll add-in, which allows you to re-write the main formula as:

=IF(ISNA(SETV(MATCH(A2,$CB$2:$CB$7,0))),
    "Not Found",
   IF(INDEX($CI$2:$CI$7,GETV())=H2,
     IF(INDEX($CJ$2:$CJ$7,GETV())=I2,
       "OK",
       "CHECK"),
     "CHECK"))

 

[jbeaucaire]

In AL2 and copied down:

=IF(COUNTIF($CB:$CB,A2)=0,"Security Not Found",IF(AND(H2=CI2,I2=CJ2),"OK","CHECK"))

 

[carddard]

Thanks so much. Problem solved.

 

[carddard]

jbeaucaire, I noticed that your code has a fault.

It only matches H=CI,I=CJ if they are on the same row.
In my case, I need to match them even if they are not on the same row.

 

[jbeaucaire]

Please provide a better dateset in your test file that outlines good examples off all the obstacles that need to be overcome.

 

[carddard]

Ok. Here's the same data set, but rearranged.

No. 3 should be CHECK instead of OK.

 

[jbeaucaire]

Actually 2 & 3 are bad...try this out in AL2 and copied down:

=IF(COUNTIF($CB:$CB,A2)=0,"Security Not Found",CHOOSE(SUMPRODUCT(--($CB$2:$CB$1000=A2),--($CI$2:$CI$1000=H2),--($CJ$2:$CJ$1000=I2))+1,"Check","OK"))

 

[carddard]

It works good!

But does that mean it only works for the first 1000 rows?

By the way, here's the code that I've come up with:

With Sheets("FD")
    lastrowA = .Range("A" & Rows.Count).End(xlUp).Row
    
    With Range("AL2:AL" & lastrowA)
        '=IF(COUNTIF($CB:$CB,A17)=0,"Not Found In Month End File","CHECK")
        .FormulaR1C1 = "=IF(COUNTIF(C80,RC[-37])=0,""Not Found In Month End File"",""CHECK"")"
    End With
End With

'If H = CI and I = CJ (ie. DATE & PRICE match), display OK. Otherwise display CHECK.
Sheets("FD").Select
n = Rows.Count
[al1].Resize(n) = [al1].Resize(n).Value
arr = [a1].Resize(n, 88) 'Resize to CJ (Price)

For a = 2 To Sheets("FD").Range("A" & n).End(xlUp).Row

    If arr(a, 38) = "CHECK" Then

        For i = 2 To Sheets("FD").Range("CB" & n).End(xlUp).Row
            If arr(a, 1) = arr(i, 80) Then 'MATCH SECURITY
                If arr(a, 8) = arr(i, 87) Then 'MATCH DATE
                    If arr(a, 9) = arr(i, 88) Then 'MATCH PRICE
                        Cells(a, 38) = "OK" 'Display in column AL
                    End If
                End If
            End If
        Next
    End If
Next

 

[shemayisroel]

It works good!

But does that mean it only works for the first 1000 rows?
[/code]

Yes but you can obviously modify to suit.

Did you try Aladin's solution?