Formula to see if row is duplicated in table

Hi and thanks for any help that you can provide. I need to insert a column to return the value yes if any row is the exact duplicate of another row within the whole table. I'm trying to do this using with a formula and not conditional formatting. Is there a way to do this?

LastName FirstName MiddleName BirthDt
Jones Tom 2/1/1950
smith sam B 5/1/1943
SIMPSON EVERETT T 1/2/2014
SIMPSON JAMES 3/4/2012
Simpson EverETT T 8/1/2014


Thanks for at least taking the time to read my problem.

In a new column say column E you could concatenate all the data in columns A to D and then in column F you could use the countif function somehting like =COUNTIF($F$2:$F$100,F2)

Originally Posted by chrispittelli

In a new column say column E you could concatenate all the data in columns A to D and then in column F you could use the countif function somehting like =COUNTIF($F$2:$F$100,F2)

That would be a round a bout way because then I would have to remove the ones that return a value more than 1. I'm trying to do this in power query by maybe adding a custom column.

I feel like there should be a more direct route of doing this.

Does it matter whether "yes" or number > 1 indicates a duplicate: What is your idea of a"custom column"?

The Concatenation is probably the easiest way to achieve what you want.

Originally Posted by JohnTopley

Does it matter whether "yes" or number > 1 indicates a duplicate: What is your idea of a"custom column"?

The Concatenation is probably the easiest way to achieve what you want.

I'm trying to remove the distinct rows. Any row that has the same Last Name, First Name, Middle Name, and Date of Birth values as another row in the table I wan't to keep. So instead of removing duplicates, I'm removing rows that aren't duplicates. Does that make sense?

I'm not sure how familiar you are with power query but I can add custom columns within power query. However there isn't a way to replicate a countif formula in power query.

Thanks!

So filter on results that are 1 and copy/paste.

Originally Posted by JohnTopley

So filter on results that are 1 and copy/paste.

That's an option but it's not a good option being that this is a table that I'm connected to with a live database. Which means every time you refresh the workbook it will delete any columns that you manually add for a calculation. I'd like to be able to do this within the query itself. If that makes sense.

You could copy and paste the table to another sheet. Highlight the data on your new sheet then use the built in function of remove duplicates