# Challenging problem

#### carddard

##### Active Member
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:

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

3) Otherwise display "CHECK".

I have attached a sample file HERE.

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.

##### MrExcel MVP
Try...
Code:
``````=IF(ISNA(MATCH(A2,\$CB\$2:\$CB\$7,0)),
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),
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))),
IF(INDEX(\$CI\$2:\$CI\$7,GETV())=H2,
IF(INDEX(\$CJ\$2:\$CJ\$7,GETV())=I2,
"OK",
"CHECK"),
"CHECK"))``````

#### jbeaucaire

##### Well-known Member
In AL2 and copied down:

#### carddard

##### Active Member
Thanks so much. Problem solved.

#### carddard

##### Active Member
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

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

#### carddard

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

No. 3 should be CHECK instead of OK.

#### jbeaucaire

##### Well-known Member
Actually 2 & 3 are bad...try this out in AL2 and copied down:

#### carddard

##### Active Member
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)
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

##### Well-known Member
It works good!

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

Yes but you can obviously modify to suit.

Replies
9
Views
453
Replies
5
Views
401
Replies
3
Views
357
Replies
2
Views
478
Replies
0
Views
129

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.

### Which adblocker are you using?

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

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