Detect and list duplicates

[Briuum]

Good day to all,

I have a sheet as listed below. I am searching for a formula that finds duplicates and lists them in P13:P16. The values should be checked for duplicates between B3:K11 and M3:S10. If there would be more duplicates it only lists the top 4 duplicates alphabeticly.

I have found a formula that works in one colomn as listed below. But in cant get it to work on the above sheet.

A lot of thanks in advance for helping out.

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

If you put this formula in P13 does it do what you want?

=LET(L,TOCOL(M3:S10),TAKE(SORT(FILTER(L,ISNUMBER(MATCH(L,TOCOL(B3:K11),0)),"")),4))

 

[Briuum]

Thank you for the quick reply. I will look into the XL2BB.

The formula you suggested contanies ... ,TAKE(

Is this correct. I can't find this formula in excel.

 

[Briuum]

My bad, I found the translation to dutch. Thanks you for the help.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Briuum]

What would the formula be if i was looking for only unique(non dupelicates) names in these cells?

 

[Peter_SSs]

What would the formula be if i was looking for only unique(non dupelicates) names in these cells?

That is not clear to me. Do you mean ..

1. Items that are in M3:S10 that are not in B3:K11?
2. Items that are in B3:K11 that are not in M3:S10?
3. Items that are in B3:K11 or M3:S10 but only appear once in total?
4. Something else?

Perhaps a small set of representative sample data and the expected results with an explanation in relation to that sample data might help clarify. Refer to me previous comment regarding sample data:

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

 

[Briuum]

I can't get XL2BB to work on excel online... Think I'm doing something wrong...

I would like to make a list of unique names in Q13:Q16. Comparing B3:K11 with M3:S10, --> Items that are in M3:S10 that are not in B3:K11
I can't find a way to make this work :s

Thank you all in advance for the help.

 

[Peter_SSs]

I can't get XL2BB to work on excel online...

It doesn't work with Excel online but your profile says you have 365, 2021 and 2019?

Try

=LET(L,TOCOL(M3:S10),TAKE(SORT(FILTER(L,ISNA(MATCH(L,TOCOL(B3:K11),0))*(L<>""),"")),4))