what you want to do with duplicates across worksheets, remove one of the records
anyway, if you are using 2010, 2013 (Add-ons) or 2016 and above, Power Query is a great tool to handle remove duplicates
let us know your issues or upload a mock up file for us
Thanks for your time. I checked out both the methods suggested by you.
The Power Query method was more helpful and helped me consolidate the sheets. I converted the data into a table and then used a countif function to reveal the duplicate values.