Return Missing Values Column 1 vs. Column 2

[Luke4]

Hi,

I have two departments. Operations and Finance.
Operations sends an completed project for invoicing and log it in a column on a sheet only used by Operations
Finance receives the invoice and process the invoice to get paid, but I have hundreds of clients thus every client pays the invoices on different times.
My issue is over 15 years a lot of information has been captured.

I'm looking for a formula that can compare my operations invoices send (Column 1) with Finances paid invoices (Column 2) and return all invoices not paid in Column 3
The other formula I want is to compare Column 1 and Column 2 and return all invoices that is not found in column 2 but is in column 1. i.e. look for duplicate invoices/find matching invoices between two column but return only invoices that is not duplicated in 2 columns

 

[Andrew Fergus]

This could probably be done with a fancy formula, but here is a method that uses a helper column instead. Assuming your data starts in row 2 and you have non-numeric headings in the first row, then enter the following formula into cell C2 and copy down for the same number of rows as there is data in column A:
=MAX(C$1:C1)+ISERROR(MATCH(A2,B:B,0))

Then in cell D2, enter the following formula and copy it down as far as you like:
=IFERROR(INDEX(A:A,MATCH(ROW(C1),C:C,0)),"")

This will give you a list of invoice numbers from column A that are not in column B and show the results in column D.

 

[Luke4]

Hi,

I have two departments. Operations and Finance.
Operations sends an completed project for invoicing and log it in a column on a sheet only used by Operations
Finance receives the invoice and process the invoice to get paid, but I have hundreds of clients thus every client pays the invoices on different times.
My issue is over 15 years a lot of information has been captured.

I'm looking for a formula that can compare my operations invoices send (Column 1) with Finances paid invoices (Column 2) and return all invoices not paid in Column 3
The other formula I want is to compare Column 1 and Column 2 and return all invoices that is not found in column 2 but is in column 1. i.e. look for duplicate invoices/find matching invoices between two column but return only invoices that is not duplicated in 2 columns

Thank you very much Andrew,
Works well.
Much Appreciated.