Excel VBA: Group on field, use another field for decision

[mburch2000]

I have an Excel file with two tabs: Source & Target. Using VBA (Macro), I am attempting to take data in Source and place in Target. The fields are normally manipulating in some form, so not a straight copy and paste. My problem: how do I group rows based on "RuleID" (1st field) and make a decision based on results in "AcctNum" (5th field) and then place results in either DID1 (6th field) or DID2 (7th field)?

See Logic below and in attached file:
If RuleID has a Key = "L" And "R" Then Scen goes in DID1 And DID2 fields.
If RuleID has a Key = "L" And <>= "R" Then Scen goes in DID2 field only.
If RuleID has a Key = "R" And <>= "L" Then Scen goes in DID1 field only.

Scen is determined below:
Dim Type, Dim Scen As String
Type = 1st two characters from left of AcctNum field: =LEFT(AcctNum, 2)
If Type = "QM" Or "CC" Then Scen = "QTOACT"
If Type = "BS" Or "IS" Then Scen = "FTOACT"


Thanks very much for help. I have been going round and round on this issue.

Mike

[NeoPa]

I tried to make sense of this but there really isn't much there. You should consider reading what you type before posting it. Referencing items in a spreadsheet as first and fifth fields is not very helpful, and so unnecessary when Excel has rows and columns and cell references to work with that are clear to everyone.

[mburch2000]

Thanks, I'll fix and repost it.. Just a tired, I guess

[NeoPa]

If I'm still up when you do I'll look it over and work it into your original question. Then I can delete these discussion posts and we can see if we can get you some help.

[mburch2000]

How do I group rows so I can make a decision about the data in another column? Fields below:

RuleID: need to group data based on this field
Descr
Op
Key: need to figure out if RuleID has Key = All L, All R or both L and R based on RuleID
AcctNUm
DID1
DID2

Sample Data is:
RuleID Descr Op Key AcctNum DID1 DID2
AB287 this L < 0 L CC560100
AB287 is L > 0 R CC562050
AB287 not L > 0 R CC562050
CN356 really L Not 0 L QM558040
CN356 important L Not 0 L QM558040
CN356 just R > 0 R BS119060
CN356 a R > 0 R BS119060
DC879 description R Not 0 L QM558040
MX289 anything R > 0 R QM558040
JJ546 field L Not 0 L QM560070
JJ546 name R < 0 R IS170500
EH561 really R < 0 L QM558040



Thanks very much for help. I have been going round and round on this issue.

Mike

[NeoPa]

Well, I guess we'll have to give up on a question that makes clear sense here and try to deduce what it is you're after :-(

You have data in columns A through G where column A (RuleID) identifies rows that are considered together as a group.
Column D (Key) will have either "L" or "R" for each row.

You want an indicator that will reflect whether each group contains rows where all Key values are "L", or all are "R" OR there are some of both.

For that you would need to take the following steps in order (NB. 999 in the code reflects the last row number of your data.) :

1. Ensure the rows are sorted by column A (RuleID) at least.
2. Put a formula in row #2 of a new column (H). This formula basically joins the two values together and is =$A2 & $D2.
3. Put a formula in row #2 of another new column (I). This formula basically counts the number of times "L" is joined to RuleID. The formula is =IF($A2=$A1,"", COUNTIF($H$2:$H $999,$A2 & "L")).
4. Put a formula in row #2 of another new column (J). This formula basically counts the number of times "L" is joined to RuleID and returns "R" if it's 0, "L" if it's the same count as RuleID on its own, or otherwise "B" for both. The formula is =IF($A2=$A1,"", IF($I2=0,"R",IF ($I2=COUNTIF($A $2:$A$999,$A2), "L","B"))).
5. Once you have these three formulas entered drag them down your whole set of rows and you will see what you're looking for (I think. Assuming I've guessed correctly as to what you want of course).

NB. Clearly I can't now delete the earlier posts otherwise nothing would make any sense.

[mburch2000]

Not exactly, but thanks for deciphering what I meant. I need the results in VBA as I am using a Macro to copy, manipulate and paste data to another tab in the file. I will try and be more clear in my next post. Thank you

[mburch2000]

I have data in columns A through G where column A (RuleID) identifies rows that are considered together as a group.
Column D (Key) will have either "L" or "R" for each row.

I want an indicator that will reflect whether each group contains rows where all Key values are "L", or all are "R" OR are both. There is no need to sort the data, as all Rows are entered in groups of RuleID. This needs to be done with VBA, as it is just part of the solution.

RuleID: need to group data based on this field
Descr
Op
Key: does Key = All L, All R or both L and R based on RuleID
AcctNUm
DID1
DID2

Sample Data is:
RuleID Descr Op Key AcctNum DID1 DID2
AB287 this L < 0 L CC560100
AB287 is L > 0 R CC562050
AB287 not L > 0 R CC562050
CN356 really L Not 0 L QM558040
CN356 important L Not 0 L QM558040
CN356 just R > 0 R BS119060
CN356 a R > 0 R BS119060
DC879 description R Not 0 L QM558040
MX289 anything R > 0 R QM558040
JJ546 field L Not 0 L QM560070
JJ546 name R < 0 R IS170500
EH561 really R < 0 L QM558040


Thanks very much for help. I have been going round and round on this issue.

Mike

[NeoPa]

OK Mike.

Determining this in VBA is fairly straightforward , but it's still unclear what you want done with the data determined.

Using formulas is straightforward as the results are shown in the column the formula is in. You haven't made it clear what you want to do with the results of the determination from the VBA though. Nor have you made it clear how any such procedure would be called in the first place. Without this information it's like trying to work in a vacuum.

At least I now have a clear understanding of the fundamentals of the question.

In case it helps, I'll provide a simple procedure that returns "L", "R" or "B" when provided with a value that matches one of those in column A in your data, and assumes that the correct worksheet is already selected (NB. This is aircode.) :

Public Function LRB(strRule As String) As String
    Dim lngRow As Long
    Dim blnLeft As Boolean, blnRight As Boolean
    Dim ranVar As Range

    Set ranVar = Range("A:A").Find(What:=strRule _
                                 , LookIn:=xlValues _
                                 , LookAt:=xlWhole _
                                 , MatchCase:=True).Activate
    If ranVar Is Nothing Then Exit Sub
    For lngRow = ranVar.Row To 1048576
        If Range("A" & lngRow) <> strRule Then Exit For
        If Range("D" & lngRow) = "L" Then
            blnLeft = True
        Else
            blnRight = True
        End If
    Next lngRow
    LRB = IIf(blnLeft, IIf(blnRight, "B", "L"), "R")
End Function