CODEWITHSUNDEEP
vbaexcel
Jason Briggs
Jason Briggs

Reputation: 13

Grouping Rows and Checking Conditions Against Them

I am using Excel VBA for the following project

I have recently started learning VBA to be more efficient at work, and have done really well with Userforms. The logic makes sense, and even though I don't know half the syntax I need, Google (and this site in particular) has made me look like an expert in no time.

I am now trying to work on a project that does not utilize Userforms, and I do not even know where to begin with the logic.

Goal: To automatically check records for deviations from a template. I have a list of materials used to create ProductX and a huge bank of data on all the times we have created ProductX. The goal is to check the List of Materials against the records to check for deviations.

Each record in the data begins with a string that denotes which run the record is associated with. 

**Example of Transaction Data:** 
             P1201-F    Salt
             P1201-F    Vinegar
             P1202-F    Salt
             P1202-F    Vinegar
             P1203-F    Salt
             P1203-F    Baking Soda

And the template says (Salt, Vinegar)

I am trying to run a program that checks P1201, then P1202, then P1203 and if the materials are not salt and vinegar (like in P1203) it will put it in a separate sheet so I can track all deviations.

I am thinking at this point, I need to define each group (P1201, P1202, P1203) as variables, and then use a check against these variables? However, I never know how many runs I am going to do.

I'm really just at a loss at how to design the logic structure behind this. I don't know if this is too open-ended either.

Upvotes: 1

Views: 76

Answers (2)

John Muggins
John Muggins

Reputation: 1198

Arrays are going to be your best bet. Make an array - probably 2-column array, of your transaction array, and make another 2 column array from your template sheet.

Suppose the following data is in range("A1:B6") on a sheet named Transaction_Data_Sheet

1. P1201-F Salt
2. P1201-F Vinegar
3. P1202-F Salt
4. P1202-F Vinegar
5. P1203-F Salt
6. P1203-F Baking Soda

dim thirdSheetRow as long, I as long, j as long
dim splitArrStrings1
dim splitArrStrings2
dim myArr1()
dim myArr2()
myArr1 = Sheets("Transaction_Data_Sheet").Range("A2:B6") ' Items to check
myArr2 = Sheets("Template_Data_Sheet").Range("A2:B600") ' Past items to check against
thirdSheetRow = 1

for I = 1 to ubound(myArr1)
   splitArrStrings1 = split(myArr1(I, 0),"-")
   For j = 1 to Ubound(myArr2)
       splitArrStrings2 = split(myArr2(J, 0),"-")
       If splitArrStrings1 = splitArrStrings2 AND uCase(myArr1(I, 1)) <> uCase(myArr2(j, 1)) Then
           Sheets("thirdSheet").Range("A" & thirdSheetRow).Value = "Row: " & I
           Sheets("thirdSheet").Range("B" & thirdSheetRow).Value = myArr1(I, 0)
           Sheets("thirdSheet").Range("B" & thirdSheetRow).Value = myArr1(I, 1)
           thirdSheetRow = thirdSheetRow + 1
       End If
    Next j
Next I

The "split" function is used to separate the "-F" from the first part of the item, ie, P1201. I also used it for your template sheet data, though I don't know if your template sheet has the trailing "-F" or not. If not then remove the "split" function from the template sheet and use the variable splitArrStrings2 for the whole value, as shown below: 

splitArrStrings2 = myArr2(j, 0)

Upvotes: 1

Cyril
Cyril

Reputation: 6829

To be clear, P1201-F is not what you're verifying, just the P1201 (e.g. Left(P1201-F,5))?

You have a few things to look into to move forward: Loops (with a dynamic last row), if-statements (to compare), and source.copy destination (on moving/copying your data).

A quick look at loops: For loops or For Each loops... I would suggest a For loop in this case, as you'll be going through the cells in a single sheet, until the last row (found dynamically).

Dim i As Long, LR as Long
LR = Cells( Rows.Count, 1).End(xlUp).Row 'if you google "VBA Last Row" you can see Rob de Bruin showing you this
For i = 2 to LR 'assumes headers in row 1
    'Do something
Next i

Your if-statement is the something you do here... very generic:

If X = Y Then 'condition
    'somethign if condition true
Else 
    'something if condition false
End If

The if-statement can have multiple criteria, linked with AND (all conditions much be met) or OR (at least one condition must be met), such as:

If X = Y AND Z = A Then

Upvotes: 1

Related Questions