Challenging problem

carddard

Active Member
Joined
Aug 19, 2008
Messages
427
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!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Try...
Code:
=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:

Code:
=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:

Code:
=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"))
 
Upvote 0

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
In AL2 and copied down:

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

carddard

Active Member
Joined
Aug 19, 2008
Messages
427
ADVERTISEMENT
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.
 
Upvote 0

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Please provide a better dateset in your test file that outlines good examples off all the obstacles that need to be overcome.
 
Upvote 0

carddard

Active Member
Joined
Aug 19, 2008
Messages
427
ADVERTISEMENT
Ok. Here's the same data set, but rearranged.

No. 3 should be CHECK instead of OK.
 
Upvote 0

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
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"))
 
Upvote 0

carddard

Active Member
Joined
Aug 19, 2008
Messages
427
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:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,195,598
Messages
6,010,646
Members
441,558
Latest member
lambierules

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top