Welcome to Kofax RPA > Reference > Basic Engine Robots > Loop in Excel > Loop Over Merged Cells

Loop Over Merged Cells

A merged cell in Excel is two or more adjacent cells merged into one cell and shown as one. You can configure your robot to loop over merged cells. The content of a merged cell is stored in the upper left cell of the cells and all other cells are blank. Looping over a table that contains merged cells can cause extraction problems. For example, if you look at the following sheet that shows test results for students, notice that some student have missed their test and in some cases, two tests are shown using a merged cell.


Looping overe Sheet with Merged Cells

Looping over the rows to extract the student test results may fail to extract the results correctly when a student has missed a test because the text "Missed" is not a number. To correct this, you can insert a test to search for the term "Missed" and then store the value 0 for a failed result. This test does not work for situations where the cell has merged. In the preceding example, this would work fine for the cell B4 because it contains the value "Missed," but it would fail to work for C4 because the content would be a blank value.

Instead of having yet another test for blank cells, you can use an If Then data converter on all Range Finders to identify a single cell inside a merged cell, and return the upper left cell of the merged cell.

  1. On the Finders tab, description field, enter Range Finder 1: Column at +2(in range named "row").
  2. In the Range field, select row.
  3. In the Use field, select Column At Position.
  4. In the Column field, select By Index.
  5. In the Offset field, enter the integer 2.
  6. In the Height field, select Same As Range and Height is to the bottom of the named range.
  7. Select Use Upper Left Cell in Merged Cells.
  8. In the Action tab, Extract This field, select Formatted Values.
  9. In the Converters field, enter an If Then statement. For example, if contains "Missed" then "0" Else INPUT.

    The Extract cell tests for the text "Missed" and uses 0 for the result. If Missed is not found it uses the extracted value.