Automation Anywhere Enterprise Excel循环验证数据冗余问题求助

Hey there! Let’s work through that Excel For Loop redundancy check issue you’re hitting in AAE—since you’re only a week in, I’ll break this down step by step with clear, actionable steps that build on what you’ve already set up (those 6 variables and 2 iterators).

First, let’s align on the goal: we need to flag duplicate Vendor ID + Material combinations in your Excel table (like the (64003, 7854786) pair that shows up twice in your sample data). Let’s optimize your existing setup and build the correct loop logic.

Step 1: Clarify Variable & Iterator Roles #

You’ve got 6 variables and 2 iterators—let’s rename/repurpose them for clarity (this will avoid confusion later):

• Iterators: Let’s call them Iterator_CurrentRow (for looping through each row of data) and Iterator_CheckDupe (if we use nested loops, though we’ll start with a more efficient single loop first)
• Current row variables: Keep POv, POm, POq as they are—these will store the Vendor ID, Material, and Quantity from the row we’re currently processing (use the Get Cell command to populate these from Excel)
• Duplicate tracking variables: Instead of single variables Vpo, Mpo, Qpo, switch to array variables like arr_VendorsChecked and arr_MaterialsChecked. Arrays are perfect here because they let us store all the vendor-material pairs we’ve already reviewed, making it easy to check for duplicates.

Step 2: Build the Efficient Single-Loop Workflow #

This is the best approach for most cases (faster than nested loops, especially with larger datasets):

1. Initialize your arrays: Use the Initialize Array command to set arr_VendorsChecked and arr_MaterialsChecked to empty arrays ([]) before starting the loop.
2. Set up the row loop: Use the For Each Row in Excel command, bind it to Iterator_CurrentRow, and set the starting row to 2 (since row 1 is your header).
3. Inside the loop:
   • Use Get Cell to pull the current row’s Vendor ID into POv (e.g., cell A{{Iterator_CurrentRow.CurrentRow}}) and Material into POm (cell B{{Iterator_CurrentRow.CurrentRow}}).
   • Use the Array Contains command to check if POv exists in arr_VendorsChecked. Store the matching index in a temporary variable like temp_MatchIndex.
   • Add an If condition: If temp_MatchIndex != -1 (meaning we found a matching vendor) and arr_MaterialsChecked[temp_MatchIndex] == POm (the material at that index matches too), then we’ve found a duplicate.
     • For duplicates: Do whatever you need—pop up a message box with the row number, mark the row in Excel, or log the duplicate to a text file.
   • If no match is found: Use Array Push to add POv to arr_VendorsChecked and POm to arr_MaterialsChecked (so we check against them in future rows).

Step 3: Troubleshooting Common Hiccups #

If your original loop wasn’t working, check these first:

• Iterator start row: Make sure you’re not including the header row (row 1) in your loop—this will cause mismatches when checking data.
• Variable binding: Double-check that your Get Cell commands are using the iterator’s current row (e.g., A{{Iterator_CurrentRow.CurrentRow}} instead of a fixed cell like A2).
• Array initialization: If you forget to initialize the arrays, AAE will throw an error when you try to push values or check for matches.

Example Command Sequence (Simplified) #

Here’s a quick breakdown of the commands in order to make this concrete:

1. Open Excel -> File path: [your Excel file path], Worksheet: Sheet1
2. Initialize Array -> Variable: arr_VendorsChecked, Value: []
3. Initialize Array -> Variable: arr_MaterialsChecked, Value: []
4. For Each Row in Excel -> Iterator: Iterator_CurrentRow, Start Row: 2, End Row: {{Excel.MaxRow}}
   4.1 Get Cell -> Cell: A{{Iterator_CurrentRow.CurrentRow}}, Store in: POv
   4.2 Get Cell -> Cell: B{{Iterator_CurrentRow.CurrentRow}}, Store in: POm
   4.3 Array Contains -> Array: arr_VendorsChecked, Search Value: POv, Store Index in: temp_MatchIndex
   4.4 If -> Condition: {{temp_MatchIndex != -1}} AND {{arr_MaterialsChecked[temp_MatchIndex] == POm}}
       4.4.1 Message Box -> Text: "Duplicate found: Vendor {{POv}}, Material {{POm}} (Row {{Iterator_CurrentRow.CurrentRow}})"
   4.5 Else
       4.5.1 Array Push -> Array: arr_VendorsChecked, Value: POv
       4.5.2 Array Push -> Array: arr_MaterialsChecked, Value: POm
5. Close Excel

Bonus: Nested Loop Approach (For Full Cross-Row Checks) #

If you need to compare every row against every other row (instead of just checking against previously processed rows), use nested loops:

1. Outer loop: For Each Row in Excel (Iterator_OuterRow), start at row 2.
2. Inner loop: For Each Row in Excel (Iterator_InnerRow), start at {{Iterator_OuterRow.CurrentRow + 1}} (so we don’t compare the same row twice).
3. In the inner loop, pull the outer row’s vendor/material and inner row’s vendor/material, then check if they match.

内容的提问来源于stack exchange，提问作者Piotr