Differences
This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
en:concepts:rowlist [2020/02/24 08:27] tiger created |
en:concepts:rowlist [2021/04/26 23:00] (current) tiger |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== Processing the rows on a worksheet ====== | + | {{indexmenu_n> |
| + | ====== 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 // | + | 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 // |
| - | 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 | + | A //row list// is a loop between the **[[en: |
| < | < | ||
| Line 9: | Line 10: | ||
| ...Process steps that use information from the current row in the row list... | ...Process steps that use information from the current row in the row list... | ||
| Row List Next | Row List Next | ||
| - | < | + | </code> |
| 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#. | 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. | ||
| ===== Example: Split data ===== | ===== Example: Split data ===== | ||
| Line 20: | Line 23: | ||
| WSheet Use: Djeeni name: wsData | WSheet Use: Djeeni name: wsData | ||
| Row List Start: Djeeni name: wsCategories | Row List Start: Djeeni name: wsCategories | ||
| - | WSheet Filter Add: on wsData column D by filtering | + | WSheet Filter Add: filter |
| WBook New: Djeeni name: wsCategoryData | WBook New: Djeeni name: wsCategoryData | ||
| Copy Range from wsData (filtered) to wsCategoryData | Copy Range from wsData (filtered) to wsCategoryData | ||
| Line 29: | Line 32: | ||
| This is what happens: | This is what happens: | ||
| - | * The row list goes 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. | + | * For each row, the data worksheet |
| - | * The new worksheet is created in a new workbook (alternatively a new worksheet can be added to an existing workbook) | + | * 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 filtered data range is copied to the newly created worksheet |
| - | * The worksheet is released to let the next worksheet be created | + | * The worksheet is released to let the next worksheet be created |
| - | * The filter is cleared to let the next filter be applied | + | * The filter is cleared to let the next filter be applied |
| - | * The next row in the row list is taken and the loop restarts | + | * The next row in the row list is taken and the loop restarts |
| + | |||
| + | ===== Example: Enrich or update data on a worksheet ===== | ||
| + | |||
| + | 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 ' | ||
| + | |||
| + | < | ||
| + | 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/ | ||
| + | * 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 | + | Read further |