Find duplicate rows across several columns with conditions and high light them

How can I find duplicates rows on several columns with conditions and high light them?

Certain conditions to check
1) if group is in ALF or Assisted Living Facility (both are same ) only high light the latest date_received record.
2)If CMS and FMID fields are different then need to keep both records. other wise keep only one record.
3)No need to check for staff number column.

Is this possible to do it in excel? Some times address have acronyms instead of full word( like Rd or Road, Av Vs Avenue).

Goal is to easily identify duplicate submissions in about 10,000 records and 45 columns.

Thank you in advance.

Finding duplicate records is generally a database feature that's not really what spreadsheet programs are for period although you can do this sort of thing in a spreadsheet program like excel. My advice personally would be to export your sheet to a Microsoft Access table and then run the query wizard because it has a find duplicates template query inside of it. then you can simply change the SQL it spits out and put a delete statement in instead of a select statement and then export the resulting table back out to excel where you got it from. I'm no expert but I've never done this in Excel without using the Ado dll library. That library allows you to query out records in an Excel spreadsheet just like a Microsoft Access query does. but the complex part of working with that library is that you have to create connection objects first.

Bo_Ry, can you explain what exactly does this do? Can I add more fields?
Thank you so much. Looks like I am close to the solution but need some time to understand this formula to expand to other duplicate fields

Some times I noticed there are 3 records, what changes can I make to captures those duplicates.
I added F and H and not sure if that is working as I have more than 50,000 records.
=MAX(INDEX($A$2:$A$7*($D$2:$D$7=$D2)*($E$2:$E$7=$E2)*($F$2:$F$7=$F2)*($H$2:$H$7=$H2)*($B$2:$B$7=$B2),))

One other issue I am having is I try to make small subset by state to handle this formula but the challenge is I need to identify the end row number to change this formula each time. Any trick?

Thank you so much for helping me