My Google-Fu is failing me.

I have one column that has about 35,000 rows.

each cell has a 6 digit Number

in the column there each value could exist 1x, 2x, 3x, or 4x

I need to quickly get a list of the cells in the column where the same value exists exactly 3 times in the entire column.

So with the example data below the only the rows with 200445, 290900 would be highlighted
I found this one but it fails over 35,000 rows so I think it is a bad formula for what I'm trying
https://www.extendoffice.com/documents/excel/5949-excel-highlight-values-that-appear-3-times.html

200221
200221
200221
200221
200445
200445
200445
212222
212222
212222
212222
220022
220022
290900
290900
290900
270000
270000
270000
270000

use ChatGPT. Seriously. It is incredible at dealing with stuff like this.

oh yeah... duh..... i even made an account for doing stuff like this.

Quick update it gave me perfect answer with my first attempt at a prompt

91_Aggie said:

---

Quick update it gave me perfect answer with my first attempt at a prompt

---

well post the solution so we can see it!

I asked it to do the same thing, thought the answer wouldn't work and re-clarified the prompt and it repeated the same instructions and so I decided to test it and turns out I'm dumb and it's smart.


To highlight values in a column that have exactly three instances, you can use Excel's conditional formatting feature. Follow these steps:

Select the column containing the values you want to highlight.
Go to the "Home" tab on the Excel ribbon.

Click on "Conditional Formatting" in the "Styles" group.

Choose "New Rule" from the dropdown menu.

In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format."

Enter the following formula in the "Format values where this formula is true" field:

=COUNTIF($A:$A,A1)=3

(Replace $A:$A with the actual range of your column, and A1 with the first cell of the selected column.)

Click on the "Format" button to choose the highlighting style you want.

Once you've set the desired format, click "OK" to apply the conditional formatting.

Now, Excel will highlight the values that appear exactly three times within the selected column.

You can do a quick pivot that shows the value and the count. You can filter those with count of 3 so you don't have to look for those that are highlighted.