Forum Discussion

MedicaidAppeals's avatar
MedicaidAppeals
Copper Contributor
Nov 09, 2022

Need help for multiple worksheets to highlight duplicates

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...
excel
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Nov 09, 2022

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)

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Nov 10, 2022

    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.

Resources