I want to update a specific column value for the specific row (same value with more than one row) in excel
Assuming you’ve got the data in a datatable, update the values in the dt then Write Range back to Excel.
I have multiple rows in excel with the same value, I want to update a particular column for all matching rows
Try the simpler approach…
Filter the original datatable for the required row value and store in a different DT, can be done using filter datatable and ‘keep’ tab in it
Filter datatable and remove those rows, can be done using filter datatable and ‘remove’ tab in it
Update the desired column and you’ll have the updated rows in new DT, can be done using for loop
Merge the DT created with the original datatable, can be done using merge datatable
Why would you do all that instead of just updating the value in the existing datatable?
Because, using “for loop” for original table can be time consuming.
Also, if you have a better solution you may post it. Let’s see what fits @arun_kumar3 's requirement best.
There are other ways using VBA too, i mentioned simpler solution to stay in contrast to the user’s question.
Take care @postwick, keep up the good work!
Your step 3 says “update the desired column” and say “can be done using for loop” - so it’s still using a for loop.
It sounds like we may have a different understanding of what the OP is trying to do. To me it sounds like basically “update mytable set [somecolumn] = ‘this’ where [somecolumn] = ‘that’”
Did I misunderstand the goal here?
Step 3 says to update the rows but only the ones that are needed to be update.
If we loop through original datatable, iteration will be done for all the entries.
Your suggestion involves scripts? If yes, then you may provide the script, @arun_kumar3 can decide to go after the best feasible solution
My suggestion is just a single For Each Row that does what you need. All that filtering and merging etc is still working with the same amount of data, and is still time-consuming where process resources are concerned. Probably more intensive because it’s multiple steps.
I suppose we’d have to test to find out which approach is more processor efficient. Maybe I’ll write a test project to get actual times for both.
So I wrote a test. It creates a datatable of however many rows we want (Name, Age, City). Default values are Mary, 28, Dallas. Every 5th row is Tom, 27, Miami. Goal is to update Tom to Boston.
Here is the single For Each Row method:
Here is your method of filtering into two separate DTs so you have one DT that’s just Tom, then remove Tom from the main dt, then For Each Row to update Miami to Boston, then merge.
I had to go to 1 million rows before any meaningful processing time difference appeared. Here is the result of 1 million rows:
Here is 5 million rows:
The filter method is significantly slower, as the first filter has to process all 5 million rows, then you update 1 million records, then you merge 5 million records.
Also, the speed at which a single For Each Row is processing millions of rows should indicate all this concern over finding other more efficient methods is pointless. For Each Row is very fast.
You say your method is faster, I say not as fast as you
That’s insightful. Thanks for the demo, efforts much appreciated!
I just took the approach of touching rows that needs update, may be any update. As it’ll be easy to locate the changed/updated rows and their count.
Again in simple for each too we can do that using counter and what not, so it depends on usecase and requirements, let’s see what @arun_kumar3 can absorb and implement.