VBA Code-- Help

[Anjali_Rani]

Hi everyone, Can any one help me to prepare VBA code for this:
I have an excel file

in which , if Name and Item have same value then the price_1 and quantity should replace in price_in_local_currency and Quantity_1.

Please help!!
Expected output:-

Please find the Excel file for the same.
VBAHelp.xlsx (10.5 KB)

[Jobin_Joy]

Hi @Anjali_Rani

Can you please share the excel file here?

Thank you.

[Anjali_Rani]

sure.
VBAHelp.xlsx (10.5 KB)

please find the attached file.

Thank you.

[Jobin_Joy]

Hi @Anjali_Rani

Please use the below code and let me know your comments on the same.

Input:

Output:

Code:
ExcelMatching.zip (10.5 KB)

Hope this will be helpful. Thank you.

[Jobin_Joy]

I just created a sample data based on your inputs. Please refer my previous post on this thread.

Thank you.

[Anjali_Rani]

Hi,Thank you for providing solution with xaml file. but i need VBA code for that, actually i have to deal with 6lakhs of data, and i think the execution time will optimize with vba code.
Please help me with vba code for the same.

Thank you

[Jobin_Joy]

Hi @Anjali_Rani

I am not much familiar with VBA. I have implemented the same logic in C# and using invoke code to execute as below.

Code:
ExcelMatchingCode.zip (10.5 KB)

@ppr @kirankumar.mahanthi1 – Can you please assist on this VBA code or tag someone who is expert on VBA?

Thank you.

[kirankumar.mahanthi1 (Kirankumar Mahanthi)]

Hi @Jobin_Joy ,

we can write vb code but there is problem i could see if we are dealing with so many rows the performance of vb code and your suggested work flow with for each loop might be same. Share your thoughts. thanks.

[Jobin_Joy]

Yes I am also thinking about the performance only. 0.6 Million records, such a huge quantity.

Any sort of LINQ expression will help here? Just a thought.

Thank you.

[ppr (Peter Preuss)]

Just to give a quick Input. The workload of operations is to Look on and has more Priority against a row Count.

In this Case the First step would be the filtering of rows which are to Update. WE can do it With LINQ and will get Back a list of datarows.

In Case of WE do need to process a full load of rows Then WE Look for parallelization Options

How many rows are to Update as IT IS Matching the particular Filter criteria?

[Anjali_Rani]

Approx more than 70lakhs of matching rows i have to update, for 3excels and each have 2lakhs of data and taking approx 6hrs.

[Anjali_Rani]

Hi , Thank you, can you modify your code little bit ,

i am getting this output, seems like “isequalto” operator not working.
and also in middle it gets 2 balnk rows.
Can you check once.Please, so i can try this code in final file.
Please find the attached xaml and excel file.
VBAHelp.xlsx (12.0 KB)
ExcelMatchingCode.xaml (13.7 KB)