Processing the rows 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 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 further process steps 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#.

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 is filtered by the category name in column C of the current row in the row list (#)
• 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:

<code>

WSheet Use wsOriginalData
Row List Start: wsAdditionsUpdates
  WSheet Filter Add: filter wsOriginalData 

Read further about row lists here