Delete rows based on comparing data in multiple columns with data in 1 column.

[zookeepertx]

Hello gurus!

I have a macro that works fine - for the most part. I'll put illustrations below to help with visualizing the problem. There are PO numbers in J, often more than one, separated by commas. There are also PO numbers starting in Z and continuing for an unknown number of columns - usually only 1 or 2, but some rows may have more.

I need it to compare Z in a row with J in the same row and, if the number in Z is NOT found in the string in J, color Z what I like to call "screaming green". If Z IS found in J, then look in AA. If AA is NOT found in J, then color AA screaming green but if AA IS in J, then look in AB. Continue this to the last populated cell in that row.

If ALL of the cells in that row, from Z to the last column, then delete the whole row. It doesn't matter if there are numbers in the string in J that aren't in the columns Z to the last column; that can be ignored.

The first screenshot is the data as it starts out, the second is how I need it to end up and the third is what that section of the macro currently does.
**(I couldn't get the cells in the table to have a green background, so I had to change the font color. So, anything with green font represent where I need the CELLS to get colored green)**

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 21"][/TD]
[TD="width: 169"]I[/TD]
[TD="width: 365"]J[/TD]
[TD="width: 115"]Z[/TD]
[TD="width: 115"]AA[/TD]
[TD="width: 115"]AB[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product ID[/TD]
[TD]P.O. In Tradeflow[/TD]
[TD="colspan: 2"]Newly Uploaded PO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]00MCE0112-00M004A|10001316[/TD]
[TD]10100341888[/TD]
[TD="align: right"]10100341888[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11123|10991535[/TD]
[TD]10100286268[/TD]
[TD="align: right"]10100355841[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]11126|10991535[/TD]
[TD]10100286268[/TD]
[TD="align: right"]10100355841[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]11127|10991535[/TD]
[TD]10100286268[/TD]
[TD="align: right"]10100355841[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]120096|10003949[/TD]
[TD]10100354437,SAMPLE[/TD]
[TD="align: right"]10100354437[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]191DR1019 0029|10000293[/TD]
[TD]10100274652[/TD]
[TD="align: right"]10100357592[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]19ECE0248-19E004A|10001316[/TD]
[TD]10100341942,10100341888[/TD]
[TD="align: right"]10100341942[/TD]
[TD="align: right"]10100355662[/TD]
[TD="align: right"]10100341888[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]19ECE0285-19E005A|10001316[/TD]
[TD]10100341942,10100341888[/TD]
[TD="align: right"]10100354447[/TD]
[TD="align: right"]10100343791[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]19ECE0297-19E009A|10001316[/TD]
[TD]10100341942,10100341888[/TD]
[TD="align: right"]10100354447[/TD]
[TD="align: right"]10100341888[/TD]
[TD="align: right"]10100357505[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]205|10000361[/TD]
[TD]10100355662[/TD]
[TD="align: right"]10100355662[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]315001|10005213[/TD]
[TD]10100357527,10100309785,10100263145,10100246653,10100215428[/TD]
[TD="align: right"]10100357527[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]315002|10005213[/TD]
[TD]10100357527,10100309785,10100263145,10100246653,10100215428[/TD]
[TD="align: right"]10100357527[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]316|10000361[/TD]
[TD]10100355662[/TD]
[TD="align: right"]10100355662[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]694|10000361[/TD]
[TD]10100355662[/TD]
[TD="align: right"]10100355662[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 21"][/TD]
[TD="width: 169"]I[/TD]
[TD="width: 365"]J[/TD]
[TD="width: 115"]Z[/TD]
[TD="width: 115"]AA[/TD]
[TD="width: 115"]AB[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product ID[/TD]
[TD]P.O. In Tradeflow[/TD]
[TD]Newly Uploaded PO[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11123|10991535[/TD]
[TD]10100286268[/TD]
[TD="align: right"]10100355841
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11126|10991535[/TD]
[TD]10100286268[/TD]
[TD="align: right"]10100355841[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]11127|10991535[/TD]
[TD]10100286268[/TD]
[TD="align: right"]10100355841[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]191DR1019 0029|10000293[/TD]
[TD]10100274652[/TD]
[TD="align: right"]10100357592[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]19ECE0248-19E004A|10001316[/TD]
[TD]10100341942,10100341888[/TD]
[TD="align: right"]10100341942[/TD]
[TD="align: right"]10100355662
[/TD]
[TD="align: right"]10100341888[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]19ECE0285-19E005A|10001316[/TD]
[TD]10100341942,10100341888[/TD]
[TD="align: right"]10100354447
[/TD]
[TD="align: right"]10100343791[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]19ECE0297-19E009A|10001316[/TD]
[TD]10100341942,10100341888[/TD]
[TD="align: right"]10100354447[/TD]
[TD="align: right"]10100341888[/TD]
[TD="align: right"]10100357505[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 21"][/TD]
[TD="width: 169"]I[/TD]
[TD="width: 365"]J[/TD]
[TD="width: 115"]Z[/TD]
[TD="width: 115"]AA[/TD]
[TD="width: 115"]AB[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product ID[/TD]
[TD]P.O. In Tradeflow[/TD]
[TD]Newly Uploaded PO[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]00MCE0112-00M004A|10001316[/TD]
[TD]10100341888[/TD]
[TD="align: right"]10100341888
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11123|10991535[/TD]
[TD]10100286268[/TD]
[TD="align: right"]10100355841[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]11126|10991535[/TD]
[TD]10100286268[/TD]
[TD="align: right"]10100355841[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]11127|10991535[/TD]
[TD]10100286268[/TD]
[TD="align: right"]10100355841[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]120096|10003949[/TD]
[TD]10100354437,SAMPLE[/TD]
[TD="align: right"]10100354437[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]191DR1019 0029|10000293[/TD]
[TD]10100274652[/TD]
[TD="align: right"]10100357592[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]19ECE0248-19E004A|10001316[/TD]
[TD]10100341942,10100341888[/TD]
[TD="align: right"]10100341942[/TD]
[TD="align: right"]10100355662
[/TD]
[TD="align: right"]10100341888[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]19ECE0285-19E005A|10001316[/TD]
[TD]10100341942,10100341888[/TD]
[TD="align: right"]10100354447[/TD]
[TD="align: right"]10100343791[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]19ECE0297-19E009A|10001316[/TD]
[TD]10100341942,10100341888[/TD]
[TD="align: right"]10100354447[/TD]
[TD="align: right"]10100341888[/TD]
[TD="align: right"]10100357505[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]205|10000361[/TD]
[TD]10100355662[/TD]
[TD="align: right"]10100355662[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]315001|10005213[/TD]
[TD]10100357527,10100309785,10100263145,10100246653,10100215428[/TD]
[TD="align: right"]10100357527[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]315002|10005213[/TD]
[TD]10100357527,10100309785,10100263145,10100246653,10100215428[/TD]
[TD="align: right"]10100357527[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]316|10000361[/TD]
[TD]10100355662[/TD]
[TD="align: right"]10100355662[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]694|10000361[/TD]
[TD]10100355662[/TD]
[TD="align: right"]10100355662[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Here is the section of coding that I have right now for that procedure:

For i = Lrow To 2 Step -1
    If Len(Range("Z" & i).Value) > 0 Then
    For j = 26 To Lcol Step 1
        If Len(Cells(i, j)) > 0 Then
            If Not (InStr(Range("J" & i).Value, (Cells(i, j).Value))) Then
            Cells(i, j).Interior.Color = RGB(80, 350, 80)
            Else
            Rows(i).Delete
            End If
        End If
    Next j
    End If
Next i

No matter what I change in the code to try and fix it, it just causes something ELSE to go wrong and I'm tired of looking at it.

Can someone please figure out what I'm doing wrong?

Thank you!

Jenny

 

[zookeepertx]

YES!! That seems to work perfectly! That report only gets run once a day and that's in the early morning, so I won't be able to check it on live data until then, but on sample data, it looks exactly right!

Thank you so much; you made my day!

(Next time this co-worker asks me for a macro, I'm going to INSIST that he give me ALL the requirements right from the start. This macro was originally created a couple of years ago and worked fine, so, no problem. Then, recently, he decided he needed to pull some more data from it, so I was able to adjust it. Then he said that wasn't what he MEANT to have it do, so, another adjustment! Most recently, he added on this requirement, which he KNEW he was going to need, but he decided to just ask for part of it at a time, thinking that all I'd have to do is adjust it again. NOPE! He's going to have to give me ALL the information right from the start!)

Anyway, thank you again.

Jenny

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[zookeepertx]

Hi Dante,

We tested the macro on live data this morning it is all good! We had someone do the whole process manually as well, just to see if they got the same result and it turned out exactly the same.

So, there are 3 of us over here that are very grateful for your help!

Jenny