Need help for multiple worksheets to highlight duplicates

[MedicaidAppeals]

Hello,

I have an Excel workbook, with 13+ worksheets. I need to find the best method (formula or vba) that can look at all worksheets, highlighting the duplicated, and place that information on a new worksheet with sheet reference and cell reference.

Thanks,

G.S.F.

[#2]

@MedicaidAppeals 

Attached is a small example with a solution proposal.

It's not exactly what you want, but maybe it will help you further in your plans.

If not helps, please just ignore it.

Thank you for your time

NikolinoDE

I know I don't know anything (Socrates)

[#3]

@NikolinoDE Thank You, Nikolino, I have made an example worksheet all within a single workbook of the problem I am talking about.

[#4]

@MedicaidAppeals 

I don't know this data as well as you so I'm not 100% certain what constitutes a duplicate when considering 13 sheets combined.

What I have done is consolidate data from your sheets and added a column to tell you which sheet name it came from.  Adding the cell reference is possible but I left it out because it may slow calculations considerably.

Stepping through the solution.

Defined name: Sheets3D - refers to the range across multiple sheets

=Alamance:Guilford!$A$6:$E$98

SheetNames.  This creates a vertical column of sheet names. I noticed the name of a given sheet was also in cell C1 in each sheet.  CHOOSEROWS and SEQUENCE used to pull the sheet name 92 times (Each sheet is allotted 92 rows for data) for each sheet and stack it.

=LET(NameStack,VSTACK(Alamance:Guilford!$C$1),CHOOSEROWS(NameStack,INT(SEQUENCE(92*COUNTA(NameStack),,1,1/92))))

Formula.  VSTACK to compile rows from all sheets. HSTACK used to join the vertical column of names from the above with the stacked rows of data. FILTER used to pull empty rows.

=LET(Stack,HSTACK(SheetNames,VSTACK(Sheets3D)),FILTER(Stack,LEN(CHOOSECOLS(Stack,2))>0))

This solution could be refined further if the definition of dupe for this data set is made known.