Search for a specific cell value in excel, if present replace the whole row with new data

[shikharno.7]

Master File.xlsx (10.3 KB)
Work File.xlsx (10.2 KB)

Hi Folks,

I have two excel files Work FIle and Master File.

I have to go into the Work File, check if new data is present in the Work File(three cases present for this example question), we grab the unique identifier cell values.

Secondly, with the help of the unique identifier in Work File, we lookup the match the data in Master File. If the unique identifier matches with cell values in the Master File, we have to simply replace/modify the whole row with the data present in the the Work File.

In this example, three cases are present in the Work File. These three cases are present in the Master File as well, so we need to copy whole row data from Work File and replace them in the Master File.

Output should look like this:

[dls (danielhlsoliveira)]

I’m not sure if i understood correctly, but if i did this would be a solution:

1 - Read Range work file → dtWorkFile
1.1 - Read Range master file → dtMasterFile
2 - If activity → dtWorkFile.RowCount>0
Then branch:
For Each Row dtMasterFile:
If activity → dtWorkFile.AsEnumerable().Any(Function(row) row.Field(Of String)(“Unique Identifier”) = currentRow.Field(Of String)(“Unique Identifier”))
Assign → CurrentRow = dtWorkFile.AsEnumerable().Where(Function(row) row.Field(Of String)(“Unique Identifier”) = currentRow.Field(Of String)(“Unique Identifier”)).CopyToDataTable()

[rushikeshlanke2]

find attached zip file.
Result file is in Output folder.

CommunityforumTask1_13_07_2023.zip (27.5 KB)

[shikharno.7]

Hi, thanks for the Xaml File. This worked perfectly.

Can I also ask you one more solution using the similar logic?

Imagine now, instead of modifying the columns, what if the data can be added (append) at the last.
The Master Sheet should look like this ?

Please refer this new Master File and Work File.

Master File.xlsx (10.3 KB)
Work File.xlsx (10.3 KB)

[rushikeshlanke2]

Please find attached zip file. Result file is in output folder.
CommunityforumTask1_20_07_2023.zip (26.9 KB)