Excel Data Table Merging

Help Studio
, ,
1

image
image
if Table A has mis match Zmasak values by matching OPH i need output mention below SC

image

2

image
image982×666 15 KB

Use This

3

Hi @sivaramana.relangi

Use the below code in Invoke Code:

' Create a new DataTable with the desired output structure
 result = New DataTable
result.Columns.Add("PRFNR", GetType(String))
result.Columns.Add("BUDAT", GetType(DateTime))
result.Columns.Add("OPH", GetType(String))
result.Columns.Add("ZMASAK", GetType(Integer))
result.Columns.Add("ZMASAK1", GetType(Integer))

' Merge the two DataTables based on the "PRFNR" and "OPH" columns
For Each row1 In dt1.AsEnumerable()
    For Each row2 In dt2.AsEnumerable()
        If row1.Field(Of String)("PRFNR") = row2.Field(Of String)("PRFNR") AndAlso
           row1.Field(Of String)("OPH") = row2.Field(Of String)("OPH") AndAlso
           row1.Field(Of Double)("ZMASAK") <> row2.Field(Of Double)("ZMASAK") Then
           
            result.Rows.Add(row1.Field(Of String)("PRFNR"),
                            row1.Field(Of DateTime)("BUDAT"),
                            row1.Field(Of String)("OPH"),
                            row1.Field(Of Double)("ZMASAK"),
                            row2.Field(Of Double)("ZMASAK"))
        End If
    Next
Next

Invoked Arguments:

image
image1105×714 31 KB

Check the below Input and Output:
Forum Input.xlsx (10.4 KB)

xaml:
Sequence6.xaml (10.3 KB)

Regards

4

Hi @sivaramana.relangi

I’ll suggest you to go ahead with performing a Join DataTable operation on OPH column.

and then filter data table, compare zmasak to zmasak_1 and keep rows where zmasak != zmasak_1

and remove columns using filter data table PRFNR_1.

Thanks

Happy Automation :smiley:

5

for Validating 4 lakh Rows it takes to much time

Table A - 4 Lakh
Table B - 4 Lakh

6

@sivaramana.relangi

Give me some time I will give you VBA Macros code.

Regards

7

Hi @sivaramana.relangi

Please check the below workflow and let me know how much time it’s taking according to your data.

image
image880×2050 101 KB

I have attached the xaml and text file for your reference
Main.xaml (18.4 KB)
FOrum.txt (2.5 KB)

Regards