Formula to search for matches, duplicates and new values between 2 columns

Hi all,

I'm trying to figure out a good, efficient formula (for 50,000 entries) that can check a list (range) of values in an unsorted column against another unsorted column to confirm if there's a match or not. The result would then be shown in a 3rd column.

- If a value shows up in New Assets but not Old Assets, return the value "New"
- If a value shows up in Old Assets but not New Assets, return the value "Replaced"
- If a value is duplicated within the New Assets column, return "Duplicate New"
- If a value is duplicated within the Old Assets column, return "Duplicate Old"

In addition, there's a difference between the total number of assets in one column compared to the other. How best should this be handled?

I have attached a sample spreadsheet. Many thanks for any assistance in advance.

Cheers, randomkiwi

Cheers for the help. I've tried the formula and it does return new assets and replaced assets - many thanks. The only issue I have is that it takes 10 mins to process with 50000 entries - wondering if there might be a quicker way or if this would be the best formula considering the type of search being conducted?

I thought it might even be best to split the formula - checking for "replaced assets" against the old assets list and new assets against the new assets list, so that the asset number can be matched against the status of the asset. This would mean that 2 extra columns would be added. Sorry if this doesn't make sense - I can come back later with more details if required.

Try

=IF(OR(A2="",B2=""),"",IF(COUNTIF(A:A,B2)=0,"New",IF(COUNTIF(B:B,A2)=0,"Replaced",IF(COUNTIF(B:B,B2)>1,"Duplicate New",IF(COUNTIF(A:A,A2)>1,"Duplicate Old","")))))

Thanks for the help guys - all these formulas are great! I understand this is a large amount of data, so a quick formula is not really possible - but a few minutes wait is not that bad either considering the number of entries. Thanks again for the assistance