Find Match and Update existing otherwise Add to list

Hello,

On the attached workbook I have a macro that places an Index/Match formula in the first tab (Charges) to search for any matches on the second tab (Updates). If it finds a match, then it will update the ChargeAmount on the Charges tab and if it does not find any matches on the Updates, then any non matches will be added to the Charges tab.

The Macro I have works; however, only with small updates. When I run the macro with all of the data (Charges has 22k rows) and Updates (630 rows), my processor resources skyrocket and brings pc to a standstill.

I am sure there is a better and more efficient way to do this, but are not sure how.

Any help would be greatly appreciated

Attached is the workbook. On the Updates tab, I have highlighted what the update is which will replace the exact match on the charge tab with the new ChargeAmount.

Macro

Please Login or Register  to view this content.

Try this:

Please Login or Register  to view this content.

hth
ikboy

@ikboy,

I tried the code that you provided, but doesn't appear to be pulling correctly.

Example: on row 7 for Item code C9248 the new charge amount, according to the Updates tab should be 800, but in column F on Charges is showing 751.32 (appears to be multiplying the original charge x 2).

What needs to happen is if the item on Charges (i'll get more specific, the ItemCode and Description) match the ItemCode and Description on Updates, then the Charge Amount should reflect the chargeamount listed on the updates tab

So on Row 7 (Charges)
C9248 | Clevidipine 0.5mg/mL 100mL SDV - 50042

This line matches on (Updates)
C9248 | Clevidipine 0.5mg/mL 100mL SDV - 50042

The ChargeAmount on Charges, should now change from 718.98 to 800.00

Thanks

amended:

Please Login or Register  to view this content.

Thank you so much ikboy, this worked great. Thank you again