Formula for checking repeat data

[CyberGuy]

I have a task with my new job that I pull an excel report every Monday. What I have to do is check what has dropped the new list that was on the old one and find the items that have been added that were not on the old list so I am checking Last Mondays list with this Mondays list. What I would like to do is add the sheet from last week to the workbook and use a formula that will tell me comparing cell B from Sheet 2 to cell B in sheet one. The following below is an example.

Cell B (Sheet One) Cell C (Sheet One)
1234 NEW
4321 DROPPED
5678 REMAINS
5555 DROPPED
5456 NEW

This will help me report to leadership our progress from eliminating these items.

Any help would be greatly appreciated.

[TMS]

Try using

Please Login or Register  to view this content.

dragged down. Any row with a value greater than 1 is a duplicate.

[CyberGuy]

Would something that fell of be 0 and then something that remains on the list would be 1? It is really three weeks of comparison but the week prior is added to the sheet to create a continuous list of findings.

Thank you for the help!

[TMS]

With only 5 rows of pseudo data, it is difficult to picture what your real life data, week on week, looks like.

See the attached example.

[CyberGuy]

I apologize I am not giving enough data. I have attached a file showing what my every Monday is but there are at least 1000 items so automation will help so much. I need to know if on the second sheet (new data) if any of the items on the first sheet is a duplicate on the second sheet, if it is a new finding or if the finding dropped off of the new sheet.

Example.xlsx

[TMS]

See the updated example.

[CyberGuy]

Thank you so much for your help with this!

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.