Anthony Horner
asked on
how to loop through and process two columns in excel
I’ve two tables on a spreadsheet. The largest (the requirements table) with is formatted in the following way:
Req ID | Parent Req ID | Some text column | Process Level ID
The smaller table (the parents table) with the parent requirements is just two columns:-
Parents Req ID | Process Level ID
I’ve been trying to write a script to go through each parent req ID in parent table and find requirements which have the same parent ID in the requirements table and then output the req record Plus the parent req ID somewhere else on the sheet where the process level ID value of the parent and the process level id of the requirement are NOT the same.
I tried to write a script (it’s been years and I’ve forgotten everything) and surprise surprise, it doesn’t work. I didn’t even know how to output the record elsewhere that I just tried to highlight the req record!
I was trying to compare values by using + 2 etc. to get the values from the next but one column
Any pointers on where I’m going wrong would be greatly appreciated.
If this can be done with a formula that I just drag down the 200 or so records then great.
Req ID | Parent Req ID | Some text column | Process Level ID
The smaller table (the parents table) with the parent requirements is just two columns:-
Parents Req ID | Process Level ID
I’ve been trying to write a script to go through each parent req ID in parent table and find requirements which have the same parent ID in the requirements table and then output the req record Plus the parent req ID somewhere else on the sheet where the process level ID value of the parent and the process level id of the requirement are NOT the same.
I tried to write a script (it’s been years and I’ve forgotten everything) and surprise surprise, it doesn’t work. I didn’t even know how to output the record elsewhere that I just tried to highlight the req record!
I was trying to compare values by using + 2 etc. to get the values from the next but one column
Any pointers on where I’m going wrong would be greatly appreciated.
Sub ProduceEligNonAligned()
'Looping through each of our output cells.
For Each g In Range("G2:G21")
For Each a In Range("A2:A193")
If g.Column = a.Column + 1 Then
If g.Column + 2 <> a.Column + 3 Then
a.Interior.ColorIndex = 37
'Exit out of this loop and move to the next output cell.
Exit For
End If
End If
'If the columns don't match
Next a
Next g
End Sub
If this can be done with a formula that I just drag down the 200 or so records then great.
It would be helpful if you provide sample workbook.
Hi,
Pls try
Pls try
Sub ProduceEligNonAligned()
'Looping through each of our output cells.
For Each g In Range("G2:G21")
For Each a In Range("A2:A193")
If g = a.Offset(,1) Then
If g.Offset(,2) <> a.Offset(,3) Then
a.Interior.ColorIndex = 37
'Exit out of this loop and move to the next output cell.
Exit For
End If
End If
'If the columns don't match
Next a
Next g
End Sub
Regards
If you are using Tables then probably Formula should work.
In attached, I am extracting Some Text Column from Requirement Table to Parent Table matching Parents Req ID & Process Level ID.
Hope this helps.
Index-Match-Multiple-Matches-Table-.xlsx
In attached, I am extracting Some Text Column from Requirement Table to Parent Table matching Parents Req ID & Process Level ID.
Hope this helps.
Index-Match-Multiple-Matches-Table-.xlsx
ASKER
apologies , attaching spreadsheet
ee-example.xlsx
ee-example.xlsx
Hi,
pls try
pls try
Sub ProduceEligNonAligned()
'Looping through each of our output cells.
For Each g In Range("f2:f21")
For Each a In Range("A2:A193")
If g.Text = a.Offset(, 1).Text Then
If g.Offset(, 1).Text <> a.Offset(, 3).Text Then
a.Interior.ColorIndex = 37
End If
End If
'If the columns don't match
Next a
Next g
End Sub
Regards
ASKER
@Rgonzo1971
That changed every cell in column A, it should only happen for cells where the associated cells process level ID is different from the parent's process level ID. So in the sheet I attached, The first record A2 should not have a highlighted cell whereas row 55 has the requirement 2418 and it has the parent ID of 910 and a process ID of 5.6.2 whereas in the other table the parent req ID of 910 (row starting at F13) has the process level ID of 5.6.4 so the cell 2418 should be highlighted in this case.
That changed every cell in column A, it should only happen for cells where the associated cells process level ID is different from the parent's process level ID. So in the sheet I attached, The first record A2 should not have a highlighted cell whereas row 55 has the requirement 2418 and it has the parent ID of 910 and a process ID of 5.6.2 whereas in the other table the parent req ID of 910 (row starting at F13) has the process level ID of 5.6.4 so the cell 2418 should be highlighted in this case.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yep, that worked.