Excluding cell values that have more than 100 variants

[haasro02]

Can someone tell me how to exclude cell values that have more than 100 variants?

i.e.

CE110 Black Small
CE110 Black Medium...

CE110 Silver 4XL

over 100 variants where common denominator is CE110, want to exclude any over 100.

Thank you!!!

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[haasro02]

Took me a while to find it, but....change 1 to 100

To find duplicate values in Excel using a formula, you can use the COUNTIF function to determine if a value appears more than once in a list:

1. Select a cell next to the first cell in the range you want to check.
2. In the formula bar, enter the formula =COUNTIF(A:A, A2)>1.
3. Excel will display "True" next to duplicates and "False" next to unique values.

 

[Cubist]

Took me a while to find it, but....change 1 to 100

To find duplicate values in Excel using a formula, you can use the COUNTIF function to determine if a value appears more than once in a list:

1. Select a cell next to the first cell in the range you want to check.
2. In the formula bar, enter the formula =COUNTIF(A:A, A2)>1.
3. Excel will display "True" next to duplicates and "False" next to unique values.

This only finds exact but not partial match as stated in your original post.

 

[Peter_SSs]

Welcome to the MrExcel board!

how to exclude cell values that have more than 100 variants?

Exclude from what?