Update sheet based on new import (remove old records and add new)

[pluginguin]

This request has to do with my wish to document which customers need to be emailed about a "late" delivery, which customers were emailed already and when they have to be updated again about the progress of their order.

To achieve this I was tinkering around with an excel spreadsheet, but I am having trouble with updating files.

I have an xlsm-file with 2 sheets. The first (called "Import") contains a link to a csv dat we can refresh whenever there is a new csv. It will then populate the sheet with completely new data.
This data has 4 columns: order date, order number, customer name and order status

The second sheet ("Emails") is supposed to be the sheet where my colleagues can manually add some data to the records.
This data is the same first 4 columns from sheet "Import" but with an additional 3 columns:

• a column where they document that they have emailed the customer (dropdown)
• a column with the date of first email (autofilled with vba script on change)
• a column with the date on which they should update the customer if the order is still late (after 4 days) (autofilled with vba script on change)

Now.... whenever there is a new version of the csv, I want to:

1. Update the sheet "Import" (already works)
2. Clear order lines from sheet "Emails" that are no longer present in sheet "Import" (because those orders have been shipped in the mean time)
3. keep order lines on sheet "Emails" that are still present on sheet "Import" unchanged.
4. add order lines from sheet "Import" that do not yet exist on sheet "Emails".

I have been recording macro's in which I perform those tasks manually and then trying to translate those to macro's to code that will work every time, but I suspect there should be an easier way to perfom the update.
Can anybody help me out please?
An example set of data on sheet "Emails" could be:
Administratie: 19095 - VehGro B.V.

Orderdate	Order#	Customer Name	Order Status	Sent mail	Emailed on	Next email
23-3-2022​	000005615	Customer ABC	Partially
3-4-2022​	000005804	Customer BCD	Partially
8-4-2022​	000005904	Customer CDE	Partially	Yes	12-05-2022	16-5-2022
11-4-2022​	000005933	Customer DEF	Partially
14-4-2022​	000005990	Customer EFG	Partially
15-4-2022​	000006005	Customer FGH	Partially