Remove/move rows that have matching values in an other dataframe

I’m currently working on managing refunding of products after some systems errors occured.
I have a huge xlxs list (table 1) with all pending refunds of a couple of weeks. However, some of the records in that table have been manually refunded and stored in a seperate file (table 2).

Here is an example of my data sets:

All refunds table:

Manually refunded table

What I now wish to do is delete (preferably move to a separate table) the rows in the ‘all refunds table’ whenever the ordernumber matches the ordernumber in the ‘manually refunded table’. Could you guys help me out?

Thanks!

>Solution :

With base R you can subset matching ordernumber’s with following code:

all_refunds <- data.frame(
  ordernumber = c(123456789, 235641458,     235984258,  283478566),
  amount = c(150.50, 250.30, 50.20, 102.45),
  status = rep("pending", 4)
)

manual_refunds <- data.frame(
  ordernumber = c(123456789, 235641458),
  amount = c(150.50, 250.30)
)

matching <- all_refunds$ordernumber %in% manual_refunds$ordernumber #Find matching ordernumbers.

You can then make a new table with matching rows and remove the rows from all_refunds table, like this:

registered_refunds <- all_refunds[matching, ] #Select only matching from all_refunds
all_refunds <- all_refunds[!matching, ] #Select rows that DO NOT match in all_refunds and reassign the table.

Giving the output:

>all_refunds
  ordernumber amount  status
3   235984258  50.20 pending
4   283478566 102.45 pending
> registered_refunds
  ordernumber amount  status
1   123456789  150.5 pending
2   235641458  250.3 pending