How to merge 2 datasets by multy criteria?

[Andrey86]

Dears,

Please help to solve that case in Power Automate desktop:

I have 2 datasets with prefilled data, need to lookup in table2 by multy statements and copy data from table2[Column4] to table1[СуммаGL]

Ur help will greatfull for me

BR

Andrey

[MichaelAnnis]

I’d do it in Excel and use Sumifs. I’m sure you could do something similar with sql, but SUMIFS in excel would definitely work. 

Use write to Excel to save them to one file with two tabs and have GPT write you a macro to fill in the column using sumifs.

then, PAD is just used to:

get tables

write tables to Excel

Run Macro

Read From Excel (now the new table is in PAD and you can do with it what you wish.)

if the tables are already in Excel and you just need to populate the column SUMIFS will do. 

good luck!

[Andrey86]

Thank you. but no. I mut use only PAD as extractor, w/o any additional 

[Sayan]

Hey @Andrey86 

Just need a confirmation that the Tables are present in a SQL server or Excel.

can your please share the sources of your data?

------------------------------------------------------------------------------------------------------------------------------

Thanks,

Sayan Patra | RPA Developer

[Andrey86]

Its easy to understand if I sent an example from PAD

Variables.CreateNewDatatable InputTable: { ^['БЕ', 'Кассовая книга', 'Основной счет', 'Начальное сальдо', 'Сумма прихода наличных', 'Сумма поступивших чеков', 'Сумма выдачи наличных', 'Остаток', 'из них наличные', 'СуммаGl', 'Разница'], [$'''KZ10''', $'''KZT1''', 240001000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT2''', 240002000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT3''', 240003000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT4''', 240004000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT5''', 240005000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT6''', 240006000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT7''', 240007000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT8''', 240008000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT9''', 240009000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ10''', 240010000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ11''', 240011000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ12''', 240012000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ13''', 240013000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ14''', 240014000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ15''', 240015000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ16''', 240016000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ17''', 240017000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ18''', 240018000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ19''', 240019000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ20''', 240020000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT1''', 240001000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT2''', 240002000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT3''', 240003000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT4''', 240004000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT5''', 240005000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT6''', 240006000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT7''', 240007000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT8''', 240008000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT9''', 240009000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ10''', 240010000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ11''', 240011000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ12''', 240012000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ13''', 240013000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ14''', 240014000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ15''', 240015000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ16''', 240016000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ17''', 240017000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ18''', 240018000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ19''', 240019000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ20''', 240020000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ21''', 240021000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ22''', 240022000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT1''', 240001000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT2''', 240002000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT3''', 240003000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT4''', 240004000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT5''', 240005000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT6''', 240006000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT7''', 240007000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT8''', 240008000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT9''', 240009000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ10''', 240010000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ11''', 240011000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ12''', 240012000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ13''', 240013000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ14''', 240014000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ15''', 240015000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ16''', 240016000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ17''', 240017000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ18''', 240018000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''] } DataTable=> DataTable
Clipboard.GetText Text=> ClipboardText
Text.SplitText.Split Text: ClipboardText StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> TextList
Variables.GenerateDataTableFromCSV.ReadCustomSeparator CSVText: TextList TrimFields: True FirstLineContainsColumnNames: False ReadFieldsAsText: False CustomColumnsSeparator: $'''|''' CSVTable=> CSVTable
Variables.DeleteColumnFromDataTable.DeleteColumnWithIndex DataTable: CSVTable ColumnIndex: 0
Variables.DeleteColumnFromDataTable.DeleteColumnWithIndex DataTable: CSVTable ColumnIndex: 3
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''* ''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''*KZ10''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''*KZ17''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''*KZ20''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''***''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''БЕ''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
SET Iter TO 0
LOOP FOREACH CurrentItem2 IN CSVTable
    IF IsEmpty(CurrentItem2[0]) THEN
        Variables.DeleteRowFromDataTable DataTable: CSVTable RowIndex: Iter
    ELSE
        Variables.IncreaseVariable Value: Iter IncrementValue: 1
    END
END

Entering data (just copy below to clipboard):

TOО "БЦК" Отд. позиции Гл. книги Дата 15.07.2024
Бухтарма Страни 1
-----------------------------------------
| БЕ |ГлавКнига | Сумма в ВВ|
-----------------------------------------
|* KZ10|240001000 | 8 378 509,45 |
|* KZ10|240002000 | 135 879,27 |
|* KZ10|240003000 | 13 726,20 |
|* KZ10|240004000 | 96 206,28 |
|* KZ10|240005000 | 0,00 |
|* KZ10|240007000 | 0,00 |
|* KZ10|240009000 | 0,00 |
|* KZ10|240010000 | 0,00 |
|* KZ10|240012000 | 502 960,27 |
|* KZ10|240013000 | 0,00 |
|* KZ10|240014000 | 0,00 |
|* KZ10|240016000 | 0,00 |
|* KZ10|240017000 | 199,00 |
|* KZ10|240018000 | 0,00 |
|* KZ10|240019000 | 0,00 |
|* KZ10|240020000 | 0,00 |
-----------------------------------------
|** KZ10| | 9 127 480,47 |
-----------------------------------------
|* KZ17|240001000 | 0,00 |
|* KZ17|240002000 | 325,00 |
|* KZ17|240003000 | 45 070,00 |
|* KZ17|240004000 | 560 640,00 |
|* KZ17|240005000 | 0,00 |
|* KZ17|240006000 | 0,00 |
|* KZ17|240007000 | 9 161,50 |
|* KZ17|240008000 | 950,00 |
|* KZ17|240009000 | 0,00 |
|* KZ17|240010000 | 209 500,00 |
|* KZ17|240011000 | 258 805,00 |
|* KZ17|240012000 | 0,00 |
|* KZ17|240013000 | 26 508,00 |
|* KZ17|240014000 | 0,00 |
|* KZ17|240015000 | 45 040,10 |
|* KZ17|240016000 | 0,00 |
|* KZ17|240017000 | 0,00 |
|* KZ17|240018000 | 0,00 |
|* KZ17|240019000 | 0,00 |
|* KZ17|240020000 | 1 482 675,00 |
|* KZ17|240021000 | 296 014,00 |
|* KZ17|240022000 | 1 372 655,00 |
-----------------------------------------
|** KZ17| | 4 307 343,60 |
-----------------------------------------
|* KZ20|240001000 | 0,00 |
|* KZ20|240003000 | 0,00 |
|* KZ20|240004000 | 135 879,70 |
|* KZ20|240005000 | 3 335,38 |
|* KZ20|240006000 | 64 900,19 |
|* KZ20|240007000 | 0,00 |
|* KZ20|240008000 | 109 776,00 |
|* KZ20|240009000 | 51 545,00 |
|* KZ20|240010000 | 0,00 |
|* KZ20|240011000 | 0,00 |
|* KZ20|240012000 | 0,00 |
|* KZ20|240013000 | 0,00 |
|* KZ20|240014000 | 979,20 |
|* KZ20|240015000 | 0,00 |
|* KZ20|240016000 | 51 375,00 |
|* KZ20|240017000 | 0,00 |
|* KZ20|240018000 | 0,00 |
-----------------------------------------
|** KZ20| | 417 790,47 |
-----------------------------------------
|*** | | 13 852 614,54 |
-----------------------------------------

[Andrey86]

Hi, it's easy to share a flow from PAD and source:

PAD script is

Variables.CreateNewDatatable InputTable: { ^['БЕ', 'Кассовая книга', 'Основной счет', 'Начальное сальдо', 'Сумма прихода наличных', 'Сумма поступивших чеков', 'Сумма выдачи наличных', 'Остаток', 'из них наличные', 'СуммаGl', 'Разница'], [$'''KZ10''', $'''KZT1''', 240001000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT2''', 240002000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT3''', 240003000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT4''', 240004000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT5''', 240005000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT6''', 240006000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT7''', 240007000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT8''', 240008000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT9''', 240009000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ10''', 240010000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ11''', 240011000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ12''', 240012000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ13''', 240013000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ14''', 240014000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ15''', 240015000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ16''', 240016000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ17''', 240017000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ18''', 240018000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ19''', 240019000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ20''', 240020000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT1''', 240001000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT2''', 240002000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT3''', 240003000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT4''', 240004000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT5''', 240005000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT6''', 240006000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT7''', 240007000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT8''', 240008000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT9''', 240009000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ10''', 240010000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ11''', 240011000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ12''', 240012000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ13''', 240013000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ14''', 240014000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ15''', 240015000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ16''', 240016000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ17''', 240017000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ18''', 240018000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ19''', 240019000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ20''', 240020000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ21''', 240021000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ22''', 240022000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT1''', 240001000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT2''', 240002000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT3''', 240003000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT4''', 240004000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT5''', 240005000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT6''', 240006000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT7''', 240007000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT8''', 240008000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT9''', 240009000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ10''', 240010000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ11''', 240011000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ12''', 240012000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ13''', 240013000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ14''', 240014000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ15''', 240015000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ16''', 240016000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ17''', 240017000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ18''', 240018000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''] } DataTable=> DataTable
Clipboard.GetText Text=> ClipboardText
Text.SplitText.Split Text: ClipboardText StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> TextList
Variables.GenerateDataTableFromCSV.ReadCustomSeparator CSVText: TextList TrimFields: True FirstLineContainsColumnNames: False ReadFieldsAsText: False CustomColumnsSeparator: $'''|''' CSVTable=> CSVTable
Variables.DeleteColumnFromDataTable.DeleteColumnWithIndex DataTable: CSVTable ColumnIndex: 0
Variables.DeleteColumnFromDataTable.DeleteColumnWithIndex DataTable: CSVTable ColumnIndex: 3
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''* ''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''*KZ10''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''*KZ17''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''*KZ20''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''***''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''БЕ''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
SET Iter TO 0
LOOP FOREACH CurrentItem2 IN CSVTable
    IF IsEmpty(CurrentItem2[0]) THEN
        Variables.DeleteRowFromDataTable DataTable: CSVTable RowIndex: Iter
    ELSE
        Variables.IncreaseVariable Value: Iter IncrementValue: 1
    END
END

Source is (copy to clipboard, this will be updated)

TOО "БЦК"                 Отд. позиции Гл. книги                Дата  15.07.2024
Бухтарма                                                        Страни         1
-----------------------------------------
|   БЕ  |ГлавКнига |          Сумма в ВВ|
-----------------------------------------
|*  KZ10|240001000 |       8 378 509,45 |
|*  KZ10|240002000 |         135 879,27 |
|*  KZ10|240003000 |          13 726,20 |
|*  KZ10|240004000 |          96 206,28 |
|*  KZ10|240005000 |               0,00 |
|*  KZ10|240007000 |               0,00 |
|*  KZ10|240009000 |               0,00 |
|*  KZ10|240010000 |               0,00 |
|*  KZ10|240012000 |         502 960,27 |
|*  KZ10|240013000 |               0,00 |
|*  KZ10|240014000 |               0,00 |
|*  KZ10|240016000 |               0,00 |
|*  KZ10|240017000 |             199,00 |
|*  KZ10|240018000 |               0,00 |
|*  KZ10|240019000 |               0,00 |
|*  KZ10|240020000 |               0,00 |
-----------------------------------------
|** KZ10|          |       9 127 480,47 |
-----------------------------------------
|*  KZ17|240001000 |               0,00 |
|*  KZ17|240002000 |             325,00 |
|*  KZ17|240003000 |          45 070,00 |
|*  KZ17|240004000 |         560 640,00 |
|*  KZ17|240005000 |               0,00 |
|*  KZ17|240006000 |               0,00 |
|*  KZ17|240007000 |           9 161,50 |
|*  KZ17|240008000 |             950,00 |
|*  KZ17|240009000 |               0,00 |
|*  KZ17|240010000 |         209 500,00 |
|*  KZ17|240011000 |         258 805,00 |
|*  KZ17|240012000 |               0,00 |
|*  KZ17|240013000 |          26 508,00 |
|*  KZ17|240014000 |               0,00 |
|*  KZ17|240015000 |          45 040,10 |
|*  KZ17|240016000 |               0,00 |
|*  KZ17|240017000 |               0,00 |
|*  KZ17|240018000 |               0,00 |
|*  KZ17|240019000 |               0,00 |
|*  KZ17|240020000 |       1 482 675,00 |
|*  KZ17|240021000 |         296 014,00 |
|*  KZ17|240022000 |       1 372 655,00 |
-----------------------------------------
|** KZ17|          |       4 307 343,60 |
-----------------------------------------
|*  KZ20|240001000 |               0,00 |
|*  KZ20|240003000 |               0,00 |
|*  KZ20|240004000 |         135 879,70 |
|*  KZ20|240005000 |           3 335,38 |
|*  KZ20|240006000 |          64 900,19 |
|*  KZ20|240007000 |               0,00 |
|*  KZ20|240008000 |         109 776,00 |
|*  KZ20|240009000 |          51 545,00 |
|*  KZ20|240010000 |               0,00 |
|*  KZ20|240011000 |               0,00 |
|*  KZ20|240012000 |               0,00 |
|*  KZ20|240013000 |               0,00 |
|*  KZ20|240014000 |             979,20 |
|*  KZ20|240015000 |               0,00 |
|*  KZ20|240016000 |          51 375,00 |
|*  KZ20|240017000 |               0,00 |
|*  KZ20|240018000 |               0,00 |
-----------------------------------------
|** KZ20|          |         417 790,47 |
-----------------------------------------
|***    |          |      13 852 614,54 |
-----------------------------------------

[Andrey86]

Hi

Source is text from clipboard:

TOО "БЦК" Отд. позиции Гл. книги Дата 15.07.2024
Бухтарма Страни 1
-----------------------------------------
| БЕ |ГлавКнига | Сумма в ВВ|
-----------------------------------------
|* KZ10|240001000 | 8 378 509,45 |
|* KZ10|240002000 | 135 879,27 |
|* KZ10|240003000 | 13 726,20 |
|* KZ10|240004000 | 96 206,28 |
|* KZ10|240005000 | 0,00 |
|* KZ10|240007000 | 0,00 |
|* KZ10|240009000 | 0,00 |
|* KZ10|240010000 | 0,00 |
|* KZ10|240012000 | 502 960,27 |
|* KZ10|240013000 | 0,00 |
|* KZ10|240014000 | 0,00 |
|* KZ10|240016000 | 0,00 |
|* KZ10|240017000 | 199,00 |
|* KZ10|240018000 | 0,00 |
|* KZ10|240019000 | 0,00 |
|* KZ10|240020000 | 0,00 |
-----------------------------------------
|** KZ10| | 9 127 480,47 |
-----------------------------------------
|* KZ17|240001000 | 0,00 |
|* KZ17|240002000 | 325,00 |
|* KZ17|240003000 | 45 070,00 |
|* KZ17|240004000 | 560 640,00 |
|* KZ17|240005000 | 0,00 |
|* KZ17|240006000 | 0,00 |
|* KZ17|240007000 | 9 161,50 |
|* KZ17|240008000 | 950,00 |
|* KZ17|240009000 | 0,00 |
|* KZ17|240010000 | 209 500,00 |
|* KZ17|240011000 | 258 805,00 |
|* KZ17|240012000 | 0,00 |
|* KZ17|240013000 | 26 508,00 |
|* KZ17|240014000 | 0,00 |
|* KZ17|240015000 | 45 040,10 |
|* KZ17|240016000 | 0,00 |
|* KZ17|240017000 | 0,00 |
|* KZ17|240018000 | 0,00 |
|* KZ17|240019000 | 0,00 |
|* KZ17|240020000 | 1 482 675,00 |
|* KZ17|240021000 | 296 014,00 |
|* KZ17|240022000 | 1 372 655,00 |
-----------------------------------------
|** KZ17| | 4 307 343,60 |
-----------------------------------------
|* KZ20|240001000 | 0,00 |
|* KZ20|240003000 | 0,00 |
|* KZ20|240004000 | 135 879,70 |
|* KZ20|240005000 | 3 335,38 |
|* KZ20|240006000 | 64 900,19 |
|* KZ20|240007000 | 0,00 |
|* KZ20|240008000 | 109 776,00 |
|* KZ20|240009000 | 51 545,00 |
|* KZ20|240010000 | 0,00 |
|* KZ20|240011000 | 0,00 |
|* KZ20|240012000 | 0,00 |
|* KZ20|240013000 | 0,00 |
|* KZ20|240014000 | 979,20 |
|* KZ20|240015000 | 0,00 |
|* KZ20|240016000 | 51 375,00 |
|* KZ20|240017000 | 0,00 |
|* KZ20|240018000 | 0,00 |
-----------------------------------------
|** KZ20| | 417 790,47 |
-----------------------------------------
|*** | | 13 852 614,54 |
-----------------------------------------

[Andrey86]

PAD flow:

Variables.CreateNewDatatable InputTable: { ^['БЕ', 'Кассовая книга', 'Основной счет', 'Начальное сальдо', 'Сумма прихода наличных', 'Сумма поступивших чеков', 'Сумма выдачи наличных', 'Остаток', 'из них наличные', 'СуммаGl', 'Разница'], [$'''KZ10''', $'''KZT1''', 240001000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT2''', 240002000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT3''', 240003000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT4''', 240004000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT5''', 240005000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT6''', 240006000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT7''', 240007000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT8''', 240008000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZT9''', 240009000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ10''', 240010000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ11''', 240011000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ12''', 240012000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ13''', 240013000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ14''', 240014000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ15''', 240015000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ16''', 240016000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ17''', 240017000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ18''', 240018000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ19''', 240019000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ10''', $'''KZ20''', 240020000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT1''', 240001000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT2''', 240002000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT3''', 240003000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT4''', 240004000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT5''', 240005000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT6''', 240006000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT7''', 240007000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT8''', 240008000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZT9''', 240009000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ10''', 240010000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ11''', 240011000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ12''', 240012000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ13''', 240013000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ14''', 240014000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ15''', 240015000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ16''', 240016000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ17''', 240017000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ18''', 240018000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ19''', 240019000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ20''', 240020000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ21''', 240021000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ17''', $'''KZ22''', 240022000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT1''', 240001000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT2''', 240002000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT3''', 240003000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT4''', 240004000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT5''', 240005000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT6''', 240006000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT7''', 240007000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT8''', 240008000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZT9''', 240009000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ10''', 240010000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ11''', 240011000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ12''', 240012000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ13''', 240013000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ14''', 240014000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ15''', 240015000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ16''', 240016000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ17''', 240017000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''], [$'''KZ20''', $'''KZ18''', 240018000, $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $'''''', $''''''] } DataTable=> DataTable
Clipboard.GetText Text=> ClipboardText
Text.SplitText.Split Text: ClipboardText StandardDelimiter: Text.StandardDelimiter.NewLine DelimiterTimes: 1 Result=> TextList
Variables.GenerateDataTableFromCSV.ReadCustomSeparator CSVText: TextList TrimFields: True FirstLineContainsColumnNames: False ReadFieldsAsText: False CustomColumnsSeparator: $'''|''' CSVTable=> CSVTable
Variables.DeleteColumnFromDataTable.DeleteColumnWithIndex DataTable: CSVTable ColumnIndex: 0
Variables.DeleteColumnFromDataTable.DeleteColumnWithIndex DataTable: CSVTable ColumnIndex: 3
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''* ''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''*KZ10''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''*KZ17''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''*KZ20''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''***''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableByColumnIndex DataTable: CSVTable AllMatches: True ValueToFind: $'''БЕ''' MatchCase: False MatchEntireCellContents: False TextToReplaceWith: $'''%''%''' ColumnNameOrIndex: 0
SET Iter TO 0
LOOP FOREACH CurrentItem2 IN CSVTable
IF IsEmpty(CurrentItem2[0]) THEN
Variables.DeleteRowFromDataTable DataTable: CSVTable RowIndex: Iter
ELSE
Variables.IncreaseVariable Value: Iter IncrementValue: 1
END
END

[Andrey86]

PAD flow and Source attached

[Sayan]

Hey @Andrey86 

If you have Two DataTable have same number of rows please check the following solution Just modify the Column Names and the Index Number.

**REGION Set Variable
SET FirstCondition TO $'''False'''
SET SecondCondition TO $'''False'''
Variables.CreateNewDatatable InputTable: { ^['Column1', 'Column2', 'Column3'], [$'''KZ10''', 240001000, $''''''], [$'''KZ10''', 240002000, $''''''], [$'''KZ10''', 240003000, $''''''], [$'''KZ17''', 240001000, $''''''], [$'''KZ17''', 240002000, $''''''], [$'''KZ17''', 240003000, $''''''], [$'''KZ17''', 240004000, $''''''] } DataTable=> DataTable1
Variables.CreateNewDatatable InputTable: { ^['Column1', 'Column2', 'Column3'], [$'''KZ10''', 240001000, 837850945], [$'''KZ10''', 240002000, 12387925], [$'''KZ10''', 240003000, 78945], [$'''KZ17''', 240001000, 45678], [$'''KZ17''', 240002000, 12345], [$'''KZ17''', 240003000, 12345], [$'''KZ17''', 240004000, 75395] } DataTable=> DataTable2
SET RowCount TO DataTable1.RowsCount - 1
**ENDREGION
LOOP LoopIndex FROM 0 TO RowCount STEP 1
SET IndexNumber TO 0
IF DataTable1[LoopIndex][0] = DataTable2[LoopIndex][0] THEN
SET FirstCondition TO $'''True'''
END
IF DataTable1[LoopIndex][1] = DataTable2[LoopIndex][1] THEN
SET SecondCondition TO $'''True'''
END
IF FirstCondition = SecondCondition THEN
SET DataTable1[LoopIndex][2] TO DataTable2[LoopIndex][2]
SET FirstCondition TO $'''False'''
SET SecondCondition TO $'''False'''
END
Variables.IncreaseVariable Value: IndexNumber IncrementValue: 1
END

If you need anything, please reply.

 ------------------------------------------------------------------------------------------------------------------------------

If my suggestion helps you, please gives it a Thumbs up 👍 and Marks as a Solution ✔️. Which motivates to solve more problems.

Thanks,

Sayan Patra | RPA Developer

[Andrey86]

No, they have a different columns and rows count. And statments (rows in table2) can be moved and not stay at the same line

[VishnuReddy1997]

Hi @Andrey86 ,

If Both the Table Data is in the excel or if you can store them in excel .Please try the Vloopup with VBScript.

I have tested this Vbscript and its working.Please find the Vbscript.

Use the Run Vbscript action and paste the below code.

Vbscript Code:

Dim xlApp, xlInputWorkbook, xlOutputWorkbook, xlInputSheet, xlOutputSheet
Dim lastRowInput, lastRowOutput, i, j
Dim inputKeyA, inputKeyC, outputKeyA, outputKeyB, outputValueC

' Create Excel application
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False ' Set to True if you want to see the Excel windows

' Open the input and output workbooks
Set xlInputWorkbook = xlApp.Workbooks.Open("C:\Users\Desktop\Power Automate Desktop\Practice\Excel\Input1.xlsx") ' Replace with your input file path
Set xlOutputWorkbook = xlApp.Workbooks.Open("C:\Users\Desktop\Power Automate Desktop\Practice\Excel\Input2.xlsx") ' Replace with your output file path

' Assuming data is on the first sheet of both workbooks
Set xlInputSheet = xlInputWorkbook.Sheets(1)
Set xlOutputSheet = xlOutputWorkbook.Sheets(1)

' Find the last row with data in the input and output sheets
lastRowInput = xlInputSheet.Cells(xlInputSheet.Rows.Count, "A").End(-4162).Row ' -4162 is xlUp
lastRowOutput = xlOutputSheet.Cells(xlOutputSheet.Rows.Count, "A").End(-4162).Row

' Loop through the rows of the input sheet
For i = 2 To lastRowInput ' Start from 2 to skip header
    inputKeyA = xlInputSheet.Cells(i, 1).Value ' Column A
    inputKeyC = xlInputSheet.Cells(i, 3).Value ' Column C

    ' Loop through the rows of the output sheet
    For j = 2 To lastRowOutput ' Start from 2 to skip header
        outputKeyA = xlOutputSheet.Cells(j, 1).Value ' Column A
        outputKeyB = xlOutputSheet.Cells(j, 2).Value ' Column B
        outputValueC = xlOutputSheet.Cells(j, 3).Value ' Column C

        ' Check if both columns match
        If inputKeyA = outputKeyA And inputKeyC = outputKeyB Then
            ' Place the value from output column C into input column D
            xlInputSheet.Cells(i, 4).Value = outputValueC
            Exit For
        End If
    Next
Next

' Save and close the workbooks
xlInputWorkbook.Save
xlInputWorkbook.Close
xlOutputWorkbook.Close

' Quit Excel application
xlApp.Quit

' Clean up
Set xlInputSheet = Nothing
Set xlOutputSheet = Nothing
Set xlInputWorkbook = Nothing
Set xlOutputWorkbook = Nothing
Set xlApp = Nothing

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

[Sayan]

Hey @Andrey86 

Have you tried with previous code?

The image you have provided earlier in your first post there is on First Table 4 Columns are there and Second Table has 3 Columns with same Row.

As I am unable to read the Languages of the Header of the column that's why I have created Same Types of DataTable and prepared the solution.

Solution:

Copy and Paste the code:

**REGION Set Variable
SET FirstCondition TO $'''False'''
SET SecondCondition TO $'''False'''
Variables.CreateNewDatatable InputTable: { ^['Column1', 'Column2', 'Column3', 'Column4'], [$'''KZ10''', $'''KZT1''', 240001000, $''''''], [$'''KZ10''', $'''KZT2''', 240002000, $''''''], [$'''KZ10''', $'''KZT3''', 240003000, $''''''], [$'''KZ17''', $'''KZT1''', 240001000, $''''''], [$'''KZ17''', $'''KZT2''', 240002000, $''''''], [$'''KZ17''', $'''KZT3''', 240003000, $''''''], [$'''KZ17''', $'''KZT4''', 240004000, $''''''] } DataTable=> DataTable1
Variables.CreateNewDatatable InputTable: { ^['Column1', 'Column2', 'Column3'], [$'''KZ10''', 240001000, 837850945], [$'''KZ10''', 240002000, 12387925], [$'''KZ10''', 240003000, 78945], [$'''KZ17''', 240001000, 45678], [$'''KZ17''', 240002000, 12345], [$'''KZ17''', 240003000, 12345], [$'''KZ17''', 240004000, 75395] } DataTable=> DataTable2
SET RowCount TO DataTable1.RowsCount - 1
**ENDREGION
LOOP LoopIndex FROM 0 TO RowCount STEP 1
SET IndexNumber TO 0
IF DataTable1[LoopIndex][0] = DataTable2[LoopIndex][0] THEN
SET FirstCondition TO $'''True'''
END
IF DataTable1[LoopIndex][2] = DataTable2[LoopIndex][1] THEN
SET SecondCondition TO $'''True'''
END
IF FirstCondition = SecondCondition THEN
SET DataTable1[LoopIndex][3] TO DataTable2[LoopIndex][2]
SET FirstCondition TO $'''False'''
SET SecondCondition TO $'''False'''
END
Variables.IncreaseVariable Value: IndexNumber IncrementValue: 1
END

Hope Now it will work!.

 ------------------------------------------------------------------------------------------------------------------------------

If my suggestion helps you, please gives it a Thumbs up and Marks as a Solution. This motivates to solve more 

problems.

Thanks,

Sayan Patra | RPA Developer