CODEWITHSUNDEEP
excel-formuladuplicatesworksheet
KMH1
KMH1

Reputation: 1

Return location of duplicate values from multiple sheets in excel

I am looking for a simpler formula to identify on which sheet duplicates occur on multiple sheets. I have been able to achieve this with a formula: =IF(COUNTIF(RDFR!$T$5:$T$10;global_data!$T8);"RDFR"&IF(COUNTIF(RDIT!$T$5:$T$10;global_data!$T8);"RDIT"&IF(COUNTIF(RDSE!$T$5:$T$10;global_data!$T8);"RDSE";""))) - but if the data is not found in one of the sheets it results in "False", I wish to return the two names of sheets where it does actually duplicate yet still look across all sheets simultaneously. If this is not possible my solution is to create the IF(COUNTIF(...in multiple combinations making the formula very large. I have a total of 10 worksheets where 9 need to be compared to the first.

I have also been able to do this with an extra long multiple IF and vlookup function. But, I am again needing to repeat the equation too many times.

Upvotes: 0

Views: 145

Answers (1)

Dave Thunes
Dave Thunes

Reputation: 280

If I understand correctly, you want something like this:

=CHOOSE(
   {1;2;3},
   IF(COUNTIF(RDFR!$T$5:$T$10,global_data!$T8),"RDFR",""),
   IF(COUNTIF(RDIT!$T$5:$T$10,global_data!$T8),"RDIT",""),
   IF(COUNTIF(RDSE!$T$5:$T$10,global_data!$T8),"RDSE","")
)

It will return a spill formula of 3 cells with either the sheet name, or a null string. If you want to remove the null strings, use the filter function.

Upvotes: 0

Related Questions