Flagging Rows based on Several Variables within the Row

[StateStreet]

Hi,
I have ten of thousands of rows and about 50 columns with values. I want to flag some of the rows with one of several flags, if the row matches three criteria. I'll jump right into the specifics, as I think that'll be the clearest:

The first row is headers. So starting with row 2 I want to check to see if column J matches one of about 30 different values. To keep from using real company data, lets say those values are dozens of different fruits & vegtables. If it is an apple, orange, or banana I want it to check another column's values.

The next column to check will be column K and should check to see if the colors match. So an apple should either be "red", "green", or "yellow". A banana should either be "yellow" or "yellowish-green". The orange should be "orange."

If any of these match, it should then check the T column to check weight. If it was an apple and the color was yellow, the weight should be 22.5 but if it was red it should be 21.5 and, if green, 20. If it was a banana and "yellowish-green" then it weigh 20 but, if yellow, 21. The orange, if orange, should weight 23.

If the rows matches all three criteria for any of the fruits, then it should return a value in column A that says "Good fruit" and that leaves column A blank if not a match. But it should also put a value in column U if a match. If, for example, the apple matched all three criteria then column U should read "Good apple" and leaves column U blank if the row wasn't a match.

Then it should increment down to the next row and carry out the same operation. It has to do this at least 12,000 times and the data will vary in length from 12,000 to maybe 19,000 rows.

Can anyone help? I understand that what I am asking may be complicated and appreciate any help any readers can provide.

Best
Dave

[snb]

It would be very helpful to post a sample workbook....

[StateStreet]

Here's an example workbook, with an example of the original data and the desired final product, with the desired parameters typed out, thanks again for helping.

[Paul_Hossler]

I used col T not U since you had T in the sample

Changed the Group strings from your requirements to match what was in the sample

Many ways to do it. I used Selects, but IF/Then's would work


[vba]
Option Explicit
'Check each row to see if the criteria meets the parameters.
'If the "LOB_Code" is ERPK, CSNA, or GL
' Then check if "Groupname" is Carrier Parent 1 or Carrier Parent 2.
' If Carrier Parent 1, then check if "Com Rate %" is 22.5% and, if a match, make the cell in column U equal to "Flag 1"
' If Carrier Parent 2, then check if "Com Rate %" is 20% and, if a match, make the cell in column U equal to "Flag 1"
'If the "LOB_Code" is CPKG, EPKG, or ComPKG
' Then check if "GroupName" is Carrier Parent 2 or Carrier Parent 4
' If Carrier Parent 2, then check if "Com Rate %" is 22%, and if a match, make the cell in column U equal to "Flag 2"
' If Carrier Parent 4, then check if "Com Rate %" is 20%, and if a match, make the cell in column U equal to "Flag 2"
'If the row's column U cell has any value, the cell in column A should read "Flagged"
Sub Flag()
Const iLOB As Long = 8
Const iGroup As Long = 10
Const iCommRate As Long = 19
Const iFlagged12 As Long = 20 'Col T -- not col U
Const iFlag As Long = 1

Dim rRow As Range, rData As Range

Set rData = ActiveSheet.Cells(1, 1).CurrentRegion
Set rData = rData.Cells(2, 1).Resize(rData.Rows.Count - 1, rData.Columns.Count)

For Each rRow In rData.Rows
With rRow

Select Case .Cells(iLOB).Value
Case "ERPK", "CSNA", "GL"

Select Case .Cells(iGroup).Value

Case "Carrier Parent Group 1"
If .Cells(iCommRate).Value = 0.225 Then
.Cells(iFlagged12).Value = "Flag 1"
.Cells(iFlag).Value = "Flagged"
End If

Case "Carrier Parent Group 2"
If .Cells(iCommRate).Value = 0.2 Then
.Cells(iFlagged12).Value = "Flag 1"
.Cells(iFlag).Value = "Flagged"
End If
End Select


Case "CPKG", "EPKG", "ComPKG"
Select Case .Cells(iGroup).Value

Case "Carrier Parent Group 2"
If .Cells(iCommRate).Value = 0.22 Then
.Cells(iFlagged12).Value = "Flag 2"
.Cells(iFlag).Value = "Flagged"
End If

Case "Carrier Parent Group 4"
If .Cells(iCommRate).Value = 0.2 Then
.Cells(iFlagged12).Value = "Flag 2"
.Cells(iFlag).Value = "Flagged"
End If
End Select
End Select
End With
Next

End Sub
[/vba]

Paul

[snb]

I think in the final data sheet rows 14 & 15 do not match the criteria specified.

[vba]
Sub snb()
sn = Sheets("originaldata").Cells(1).CurrentRegion

For j = 2 To UBound(sn)
Select Case Trim(sn(j, 8)) & Right(sn(j, 10), 1) & Format(sn(j, 19), "0.000")
Case "ERPK10,225", "ERPK20,20", "CSNA10,225", "CSNA20,20", "GL10,225", "GL20,20"
sn(j, 20) = "Flag 1"
Case "CPKG20,22", "CPKG40,20", "EPKG20,22", "EPKG40,20", "ComPKG20,22", "ComPKG40,20"
sn(j, 20) = "Flag 2"
End Select
if sn(j,20)<>"" then sn(j,1)="flagged"
Next

Sheets("originaldata").Cells(1).CurrentRegion = sn
End Sub
[/vba]

[Paul_Hossler]

Agree that 14 is incorrectly 'Flagged' in the OP's 'To Be', but I think row 15 is correctly flagged: CPKG, Carrier Parent Group 2, and 22%

In Sub snb ()

for row j = 15 ...

?Trim(sn(j, 8)) & Right(sn(j, 10), 1) & Format(sn(j, 19), "0.000")

CPKG20.220

which is not caught by your Case "CPKG20,22",

Paul

[snb]

@PH

Thank you !

Which lead me to this improvement:

[vba]
Sub snb()
sn = Sheets("originaldata").Cells(1).CurrentRegion

For j = 2 To UBound(sn)
Select Case Trim(sn(j, 8)) & Right(sn(j, 10), 1) & Format(sn(j, 19), "0.000")
Case "ERPK10,225", "ERPK20,200", "CSNA10,225", "CSNA20,200", "GL10,225", "GL20,200"
sn(j, 20) = "Flag 1"
Case "CPKG20,220", "CPKG40,200", "EPKG20,220", "EPKG40,200", "ComPKG20,220", "ComPKG40,200"
sn(j, 20) = "Flag 2"
End Select
If sn(j, 20) <> "" Then sn(j, 1) = "flagged"
Next

Sheets("originaldata").Cells(1).CurrentRegion = sn
End Sub
[/vba]

or

[VBA]
Sub snb_002()
sn = Sheets("originaldata").Cells(1).CurrentRegion

For j = 2 To UBound(sn)
sn(j, 1) = UBound(Split(Split("_ER10,225_ER20,200_CS10,225_CS20,200_GL10,225_GL20,200_ CP20,220_CP40,200_EP20,220_EP40,200_Co20,220_Co40,200_", "_" & Left(Trim(sn(j, 8)), 2) & Right(sn(j, 10), 1) & Format(sn(j, 19), "0.000_"))(0), "_"))

If sn(j, 1) < 13 Then sn(j, 20) = "Flag " & sn(j, 1) \ 6 + 1
sn(j, 1) = IIf(sn(j, 1) = 13, "", "Flagged")
Next

Sheets("originaldata").Cells(1).CurrentRegion = sn
End Sub
[/VBA]

[StateStreet]

I used col T not U since you had T in the sample

Changed the Group strings from your requirements to match what was in the sample

Many ways to do it. I used Selects, but IF/Then's would work


[vba]
Option Explicit
'Check each row to see if the criteria meets the parameters.
'If the "LOB_Code" is ERPK, CSNA, or GL
' Then check if "Groupname" is Carrier Parent 1 or Carrier Parent 2.
' If Carrier Parent 1, then check if "Com Rate %" is 22.5% and, if a match, make the cell in column U equal to "Flag 1"
' If Carrier Parent 2, then check if "Com Rate %" is 20% and, if a match, make the cell in column U equal to "Flag 1"
'If the "LOB_Code" is CPKG, EPKG, or ComPKG
' Then check if "GroupName" is Carrier Parent 2 or Carrier Parent 4
' If Carrier Parent 2, then check if "Com Rate %" is 22%, and if a match, make the cell in column U equal to "Flag 2"
' If Carrier Parent 4, then check if "Com Rate %" is 20%, and if a match, make the cell in column U equal to "Flag 2"
'If the row's column U cell has any value, the cell in column A should read "Flagged"
Sub Flag()
Const iLOB As Long = 8
Const iGroup As Long = 10
Const iCommRate As Long = 19
Const iFlagged12 As Long = 20 'Col T -- not col U
Const iFlag As Long = 1

Dim rRow As Range, rData As Range

Set rData = ActiveSheet.Cells(1, 1).CurrentRegion
Set rData = rData.Cells(2, 1).Resize(rData.Rows.Count - 1, rData.Columns.Count)

For Each rRow In rData.Rows
With rRow

Select Case .Cells(iLOB).Value
Case "ERPK", "CSNA", "GL"

Select Case .Cells(iGroup).Value

Case "Carrier Parent Group 1"
If .Cells(iCommRate).Value = 0.225 Then
.Cells(iFlagged12).Value = "Flag 1"
.Cells(iFlag).Value = "Flagged"
End If

Case "Carrier Parent Group 2"
If .Cells(iCommRate).Value = 0.2 Then
.Cells(iFlagged12).Value = "Flag 1"
.Cells(iFlag).Value = "Flagged"
End If
End Select


Case "CPKG", "EPKG", "ComPKG"
Select Case .Cells(iGroup).Value

Case "Carrier Parent Group 2"
If .Cells(iCommRate).Value = 0.22 Then
.Cells(iFlagged12).Value = "Flag 2"
.Cells(iFlag).Value = "Flagged"
End If

Case "Carrier Parent Group 4"
If .Cells(iCommRate).Value = 0.2 Then
.Cells(iFlagged12).Value = "Flag 2"
.Cells(iFlag).Value = "Flagged"
End If
End Select
End Select
End With
Next

End Sub
[/vba]

Paul

First of all, thank you very much for your help Paul. I put that code into VB, ran the macro, and it worked great.

SNB, thanks very much for your comments although to be honest I am now slightly confused. I see that you both agree the code need additional revision but I'm not sure why. When I ran the example code it flagged rows 4 and 15. I believe that's correct. I don't think that the three criteria are met by any other line.

If it indeed does need the additional code, where should I insert it?

Thanks again for your help, it's greatly appreciated!

[Paul_Hossler]

When I ran the example code it flagged rows 4 and 15. I believe that's correct. I don't think that the three criteria are met by any other line.

Row 4 and 15 are the only correct ones to flag. Row 14 and the rest didn't meet your critera.

IMHO it's worth the small investment in time and effort to understand the code that anyone supplies, since it will make it easier and faster for you to make the updates that always seem to come up at the last minute when the boss is breathing down your neck.

Who knows .. you have 6 GroupNames now, but in the future you'll probably have more, and you'll be able to modify the macro to handle changes

Paul