Find duplicate values across multiple columns and multiple rows

[Preccor]

Hello,

Is there a formula to find duplicate values across multiple columns and multiple rows? The file contains 9 columns and 5600 rows. The columns consist of phone numbers. The rows are the contact names labeled as "ID". I would like to find all duplicates so that I can clean up a database.

MrExcel HelpQuestion.xlsx
	A	B	C	D	E	F	G	H	I	J
1	ID	Mobile Phone	Home Phone	Extra Phone Numbers - 1	Extra Phone Numbers - 2	Extra Phone Numbers - 3	Extra Phone Numbers - 4	Extra Phone Numbers - 5	Extra Phone Numbers - 6	Business Phone
2	1	425-213-9912				425-236-0520	425-213-9912			425-823-7397
3	2	206-890-4911	206-526-8405	425-236-0520
4	3	425-236-0520
5	4	206-753-8392	206-542-1521	425-213-9912	206-890-4911			206-753-8392
Sheet1

 

[KRice]

If you are planning to just clean up the records, have you considered using Conditional Formatting to highlight duplicates? You can then scroll through the file and decide how to resolve the duplicates on a case-by-case basis. From the Home main menu...Conditional Formatting > Highlight Cell Rules > Duplicate Values would produce something like this...

MrExcel20210308.xlsx
	A	B	C	D	E	F	G	H	I	J
1	ID	Mobile Phone	Home Phone	Extra Phone Numbers - 1	Extra Phone Numbers - 2	Extra Phone Numbers - 3	Extra Phone Numbers - 4	Extra Phone Numbers - 5	Extra Phone Numbers - 6	Business Phone
2	1	425-213-9912				425-236-0520	425-213-9912			425-823-7397
3	2	206-890-4911	206-526-8405	425-236-0520
4	3	425-236-0520
5	4	206-753-8392	206-542-1521	425-213-9912	206-890-4911			206-753-8392
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:J5	Cell Value	duplicates	text	NO

 

[Preccor]

Highlight and sift through case by case is the best solution to avoid compromising the data. Thank you. Greatly appreciated.

 

[KRice]

I'm happy to help...and good luck! I don't envy your task...sifting through 5600 rows might be a challenge.