CODEWITHSUNDEEP
excelexcel-formulavba
PMC
PMC

Reputation: 49

In Excel, return list of items from column that are listed more than three times

I have a list of categories in a column. I need to, in a cell above this list, note any categories (text) that are repeated more than three times. At first I thought I had a handle on this with count formulas or something like that, but I'm having a hard time. I couldn't find anything like this in previously asked questions.

Does anyone have any tips for this? I appreciate it.

Example:

Column D (pretend it's vertical): Red Yellow Blue Yellow Green Red Pink Orange Red Yellow Blue Green Yellow Green Red

The cell (or multiple cells) above the data would list "Yellow" and "Red" because these text strings were found more than three times.

Upvotes: 0

Views: 3408

Answers (3)

Roxton
Roxton

Reputation: 151

You've tagged this with Excel-VBA but haven't said anything else about VBA, so I assume you want a formula-based solution.

For the sake of argument, let's say that your list is in cells D2:D17.

First, we're going to get a list of unique values and put it in the E column, starting in cell E2. We need to leave a blank cell above the list for this to work (otherwise we'll get a circular reference).

In cell E2, enter this:

=INDEX(D2:D17,MATCH(0,COUNTIF($E$1:E1,D2:D17),0))

You must press ctrl+shift+Enter to cause Excel to interpret this as an array formula. (For more information about array fomulae, see this article).

This should output the first unique value in the list. You can then duplicate-drag it down to the length of the list to allow for all possible values to appear. Note that you can also create a list of duplicate values by copying your original list to a new column and using Excel's built-in duplicate removal tool (Data>Remove Duplicates). The next step will be the same either way.

In the F column, we're going to count the occurrences of each of the unique values. In cell F2, enter this:

=COUNTIF(D2:D17,E2)

Drag-duplicate that down, and you should now have a nice two-column table of the occurrences of each of the unique values in your original list. You can filter that as you see fit.

Upvotes: 1

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3823

Copy your column with duplicates to a new column [let's say column E, starting at row 2]. Go to the Data Ribbon and delete duplicates. To the right of this new no-duplicate column, put the following formula [starting at F2 and dragged down]:

=IF(COUNTIF(D:D,E2)>3,MAX($F$1:F1)+1,"")

Then you have a few options depending on how you want your data to show - if you can read it like that no further work required. If you want it to show a continous ordered list of duplicate items, then just use an INDEX/MATCH combination. For example if you want your duplicate list to start at A1 and go down, do like so:

=INDEX(E:E,MATCH(ROW(),F:F)

This will not work if the list starts anywhere other than row 1 [because of the ROW() function]. If you want your list to start elsewhere, you will need to make an index that goes up by 1, and then reference that index instead of ROW().

Upvotes: 0

user1274820
user1274820

Reputation: 8144

You can use these formulas to show you the respective Red/Yellow/Green colors.

=IF(COUNTIF([Range],"=Yellow")>=3,"Yellow","")

=IF(COUNTIF([Range],"=Red")>=3,"Red","")

=IF(COUNTIF([Range],"=Green")>=3,"Green","")

So to display "Yellow" if there are more than 3 yellows in column D, you could use:

=IF(COUNTIF(D:D,"=Yellow")>=3,"Yellow","")

Of course, to simply display the count: COUNTIF(D:D,"=Yellow")

Upvotes: 0

Related Questions