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

zookeepertx

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
589
Office Version
  1. 365
Platform
  1. Windows
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:

Code: 
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
 
Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
Call a macro based on text in column C (same row that is active).
Replies
8
Views
131
6StringJazzer
6StringJazzer
S
  • Question Question
Deleting multiple rows, columns, unmerging, pivot table all at once.
Replies
2
Views
102
HighAndWilder
H
S
  • Question Question
Macro to extract Columns based on multiple criteria
Replies
6
Views
4K
Trebor76
T
D
  • Question Question
Search Multiple Columns
Replies
2
Views
759
Dungadin
D
smide
  • Question Question
Conditional formatting formula to highlight changes based on text in two columns
Replies
1
Views
578
Eric W
Eric W

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top