Solved: Filtering Out Rule Names & Choosing Records

[rangudu_2008]

Hi,
I have a column in a worksheet that contains some rule names (8 letter words) with their condition descriptions. I do not need those descriptions for the computations that i do in the workbook via a macro. I only need the 8 letter words which i need to pass to a function in my macro code. I have 19 such rules (all rule name are represented as 8 letter codes).

For example,
Rule1 - Desc1
Rule2 - Desc2
Rule3 - Desc3
...

The thing i'm puzzled about is if 2 or more rules out of these 19 (which generate records which i process) fire records in combination, i need to filter out those rule codes into a separate column and find out the rule code that is new (which i should do by checking the rule's date).

How can i extract the rule's 8 letter code(s) from the column and check whether the rule is new or not?

Sample (which i need to check)
Rule1 - Desc1; Rule2 - Desc2; ...

Thanks for any suggestions/help are welcome.

Ranga

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim rules As Variant
Dim tmp As String

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
ReDim rules(1 To 1)
rules(1) = Left$(.Range("A1").Value, InStr(.Range("A1").Value, " -") - 1)
For i = 2 To LastRow

tmp = .Cells(i, "A").Value
tmp = Left$(tmp, InStr(tmp, " -") - 1)
If IsError(Application.Match(tmp, rules, 0)) Then

ReDim Preserve rules(1 To UBound(rules) + 1)
rules(UBound(rules)) = tmp
End If
Next i

Call MyMacro(rules)
End With

End Sub

Public Sub MyMacro(rules As Variant)
Dim i As Long

For i = LBound(rules) To UBound(rules)

Debug.Print rules(i)
Next i

End Sub
[/vba]

[rangudu_2008]

The code works fine, but i need to split the rules as described in the attachment. Irrespective of how many no. of times a rule(s) may be repeat in the column (irrespective of the order of occurrence - for eg., rule1-desc1; rule2-desc2; [or] rule2-desc2; rule1-rule1), i need to split them in whichever way they occur in the rules column.

I mean to say that i need to split the rule code literally it as it is because for each such combination, irrespective of the order of occurrence (of the rule code) it may or may not indicate the same c_id (secondary key for the row - this field is not unique) & a_id ( primary key for the row - this field is unique) in the whole sheet.

In the example stated in the above, i may need to split both as separate rows - i.e. row1: rule1-desc1;rule2-desc2; & row2: rule2-desc2;rule1-desc1 needs to be split as rule1-rule2 and rule2-rule1 separately even though the c_id fields for both rows are same. This is because my a_id field is unique for both the rows.

I need to include these split rule codes in a new column (the OUTPUT REQUIRED column) next to the Rules column in the same sheet. This column extracted & created from the rules column will be useful for further computation via the macro code that i've written in the sheet.

I also need a search module which help me find the rule code from the combination thereby helping me to randomly choose the whole row for a rule (i have already written a randomizing function for this)

Say, for example that the following combination occurs in the rule column in the sheet,

Row 1: Rule1 - Desc1; Rule2 - Desc2; Rule3 - Desc3;
Row 2: Rule1 - Desc1; Rule5 - Desc5; Rule8 - Desc8;
Row 3: Rule5 - Desc5; Rule3 - Desc3;

Suppose if i want to choose rows which contain Rule1 in the Rule column, then after splitting the codes in the rule column (i would need only the 8 letter code of the rule) i need to search & choose rows 1 & 2. Similarly, if i need the rows with Rule5, i need to search & choose rows 2 & 3.

As i will be choosing the values in the a_id column randomly, i need to first split the rule codes and then based on the rule's associated date value (which would help me determine whether to choose the rule or not if its within a certain limit - a whole number) i may decide whether to choose that row or not randomly.

In the explanation just preceding to the above paragraph, say if i need to choose all rows with the value rule5 (which i decide via code after checking the date value of rule5) then my search code should first indicate the rows which contain rule5 in the rule column [whether separately or in a combination with other rule(s)] and then i would randomly choose certain no. of rows from those rows which all have the rule code rule5 in the rule column.

One key that i found (while i wrote this posting) to split the rule code is that each rule in the rule column is split by the delimiter ';'

Can't this split be done via formulas written in an adjacent column in same sheet? How can the search be implemented easily after splitting the 8 letters rule codes?

Any easy suggestions about how this split be made easily? Can anybody help me out?

Ranga