Match / Compare values between two ranges and loop it

[prukrishnan]

Hello,

I am struggling with trying to create a macro and hope I can get some help. I have one sheet (called Sheet1) with 58 columns and about 30,000 rows of data. I am trying to check if values from another sheet appear in the first one and assign a value to against each row if it matches the criteria.

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]Unit 1[/TD]
[TD]Unit 2[/TD]
[TD]Unit 3[/TD]
[TD]Unit 4[/TD]
[TD]Unit 5[/TD]
[TD]Level 1[/TD]
[TD]Level 2[/TD]
[TD]Level 3[/TD]
[TD]Level 4[/TD]
[TD]Level 5[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If any value in requirement1 (unit 1 to 5) matches and if any value in requirement2 (Level 1 to 5) matches, then value on the last column (name) needs to appear in sheet1 against the relevant row. Each row is one set of criteria and all criteria needs to be checked against each row in Sheet1. There are about 300 rowss in the requirement sheet.

When I had only 2 units and 2 levels to check, I was able to use the following formula to identify the rows for each requirement
=Countif(Sheet1!A2:BE2,Requirement!$A2)+Countif(Sheet1!A2:BE2,Requirement!$B2)
However, this isn't working with 20 units.

Let me know if this isn't clear.

Please help!

 

[prukrishnan]

I found the formula but am unable to loop it correctly. Any help would be appreciated.

The formula for checking the first row in the requirement sheet is
IF(AND(((IF(SUMPRODUCT(COUNTIF(A2:BE2,Requirement!$C$2:$V$2)*ISNUMBER(Requirement!$C$2:$V$2)),1,0)))>0,COUNTIF(Requirement!$X$2:$AB$2,Q2)=1),Requirement!$B$2)

However, not sure how to loop it to go to the next row in requirement. For the next row, C2:V2 will change to C3:V3 and X2:AB2 will change to X3:AB3 and so on.




----------------------------

Hello,

I am struggling with trying to create a macro and hope I can get some help. I have one sheet (called Sheet1) with 58 columns and about 30,000 rows of data. I am trying to check if values from another sheet appear in the first one and assign a value to against each row if it matches the criteria.

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]Unit 1[/TD]
[TD]Unit 2[/TD]
[TD]Unit 3[/TD]
[TD]Unit 4[/TD]
[TD]Unit 5[/TD]
[TD]Level 1[/TD]
[TD]Level 2[/TD]
[TD]Level 3[/TD]
[TD]Level 4[/TD]
[TD]Level 5[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If any value in requirement1 (unit 1 to 5) matches and if any value in requirement2 (Level 1 to 5) matches, then value on the last column (name) needs to appear in sheet1 against the relevant row. Each row is one set of criteria and all criteria needs to be checked against each row in Sheet1. There are about 300 rowss in the requirement sheet.

When I had only 2 units and 2 levels to check, I was able to use the following formula to identify the rows for each requirement
=Countif(Sheet1!A2:BE2,Requirement!$A2)+Countif(Sheet1!A2:BE2,Requirement!$B2)
However, this isn't working with 20 units.

Let me know if this isn't clear.

Please help!

 

[prukrishnan]

Please can someone explain what is wrong with the following formula? I'm getting an "Application-Defined or object defined error"

Dim x As Integer

Dim NumRows As Integer
Dim Cell1 As String
Dim Cell2 As String
Dim Cell3 As String
Dim Cell4 As String
Dim Cell5 As String
Dim Rng1 As Range


Sheets("Requirement").Activate

NumRows = Range("B2", Range("B2").End(xlDown)).Rows.Count

For x = 2 To NumRows
x = 2



Cell1 = Sheets("Requirement").Cells(x, 3).Value
Cell2 = Sheets("Requirement").Cells(x, 22).Value
Cell3 = Sheets("Requirement").Cells(x, 24).Value
Cell4 = Sheets("Requirement").Cells(x, 28).Value
Cell5 = Sheets("Requirement").Cells(x, 2).Value



Sheets("Sheet1").Select


Range("BG2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(((IF(SUMPRODUCT(COUNTIF(RC[-58]:RC[-2],""Cell1"":""Cell2"")*ISNUMBER(""Cell1"":""Cell2"")),1,0)))>0,COUNTIF(""Cell3"":""Cell4"",RC[-42])=1),""Cell5"")"