Update specific column for specific row value without using for each

Help Studio
1

I want to update a specific column value for the specific row (same value with more than one row) in excel

2

Assuming you’ve got the data in a datatable, update the values in the dt then Write Range back to Excel.

3

I have multiple rows in excel with the same value, I want to update a particular column for all matching rows

4

Try the simpler approach…

  1. 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

  2. Filter datatable and remove those rows, can be done using filter datatable and ‘remove’ tab in it

  3. Update the desired column and you’ll have the updated rows in new DT, can be done using for loop

  4. Merge the DT created with the original datatable, can be done using merge datatable

5

Why would you do all that instead of just updating the value in the existing datatable?

6

Because, using “for loop” for original table can be time consuming. :slight_smile:

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! :+1:

7

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?

8

Dear @postwick

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

9

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.

10

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.

image
image594×573 11.2 KB

Here is the single For Each Row method:

image
image560×617 14 KB

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.

image
image320×788 18.1 KB

I had to go to 1 million rows before any meaningful processing time difference appeared. Here is the result of 1 million rows:

image

Here is 5 million rows:

image

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.

1 Like
11

Hey @postwick

You say your method is faster, I say not as fast as you :stuck_out_tongue:

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.