Look for row range and replace that with a looked up data from the second sheet

[manjesh_kumar]

Hi All,

I have a file called “output.xlsx” file and there are 6 columns in the “Sheet1”. I require to read from C1 to F1.

For each cell need to search corresponding value from “String Format” sheet

then replace the searched value into the corresponding range from C1 to F1.

I have attached the excel sheet for reference.
output1.xlsx (13.0 KB)

I am in a learning phase would really appreciate all your inputs and xaml file would be great.

[Marius_Puscasu]

Hi @manjesh_kumar,

You could try using something like this:

Here is the .xaml file LookForRowRangeAndReplace.xaml (9.0 KB)

Hope this helps
Best regards,
MArius

[manjesh_kumar]

Dear @Marius_Puscasu,

Good Day.

Awesome and it works as expected, that you very much.

I have just 1 more query where the row length may be dynamic, here we have defined the range C1:F1 . If suppose the range is dynamic increase from C1:F25 how to handle this issue this will happen in the real world scenario.

Regards,
Manjesh

[Marius_Puscasu]

Hi @manjesh_kumar,

I’m glad if I can help you

One workaround to C1:F1 vs (I suppose) C1:Z1 is to:
a. read initially a range greater than you need (that will cover more than 99% from your cases). Let’s say a range C1:Z1, but it depends by you
b. before Lookup Data Table to add an IF condition like this:
if Not String.IsNullOrWhiteSpace(col.ToString) then perform the two actions from the Body of For Each sequence (Lookup Data Table & Assign)

Hope this helps
Best regards,
Marius

[manjesh_kumar]

Dear @Marius_Puscasu,

The suggestion has worked . Thanks a lot for the support.

This is a great learning for me.

Regards,
Manjesh