Processing the rows/columns one by one on a worksheet

A common task is to process data in a data source according to categories that can be found on another worksheet. A good example is having a list of departments managed in a worksheet and independently some reports that should be generated separately for every department using a common data source. Djeeni uses the concept of a row/column list to solve this task.

A row list is a loop between the Row List Start and Row List Next process steps. At Row List Start you specify the worksheet and the rows on the worksheet that should be processed. Djeeni will take the rows one by one and then performs the process steps inside the loop up to Row List Next. At that point Djeeni takes the next row in the row list and repeats the process steps again:

  Row List Start
    ...Process steps that use information from the current row in the row list...
  Row List Next

You can reach any cells in the current row by referring to the appropriate column letter and #. Example: if the cell in column D of the current row in the row list must be accessed then you can type D#.

A column list works the same way looping through columns. It uses also the # sign to refer to the current column: #3.

Below is the process to split a data source on a worksheet into separate worksheets using the list of categories from yet another worksheet:

  WSheet Use: Djeeni name: wsData
  Row List Start: Djeeni name: wsCategories
    WSheet Filter Add: filter wsData column D using value wsCategories cell C#
      WBook New: Djeeni name: wsCategoryData
        Copy Range from wsData (filtered) to wsCategoryData
      WSheet Release wsCategoryData
    WSheet Filter Clear wsData
  Row List End

This is what happens:

• The row list takes the rows of the categories worksheet one by one
• For each row, the data worksheet (where column D contains category names) is filtered by the category name in column C of the current row (#) on wsCategoryData
• The new worksheet is created in a new workbook (alternatively a new worksheet can be added to an existing workbook)
• The filtered data range is copied to the newly created worksheet
• The worksheet is released to let the next worksheet be created
• The filter is cleared to let the next filter be applied
• The next row in the row list is taken and the loop restarts

Another common task is to have a worksheet with data where you should either add a new column of values or replace values in an existing column based on a list that you get in another worksheet. There is a manual solution for this task in MS Excel using the 'magical' VLOOKUP (now XLOOKUP) function. Djeeni gives you an alternative automated solution using row lists:

  WSheet Use: Djeeni name: wsOriginalData
  Row List Start: Djeeni name: wsAdditionsUpdates
    WSheet Filter Add: filter wsOriginalData column D using value wsAdditionsUpdates cell C#
      Range Set: on wsOriginalData target column using value wsAdditionsUpdates cell B#
    WSheet Filter Clear wsData
  Row List End

This is what happens:

• The original data worksheet is opened
• The row list takes the rows of the categories worksheet one by one
• For each row, the data worksheet (where column D contains some ID value) is filtered by the same ID value in column C of the current row (#) on wsAdditionsUpdates
• The value of cell B# of the wsAdditionsUpdates worksheet is added/updated on the wsOriginalData worksheet
• The filter is cleared to let the next filter be applied
• The next row in the row list is taken and the loop restarts

Read further about row lists here