Calculate Recurring Items

D

default_name

Board Regular
Joined
May 16, 2018
Messages
180
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
If I have a spreadsheet like this (below), is there a way to display the top recurring item (from cells A, B and C) in the cells in Column D, like so?
I added notes on the right side.

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Group A Item(s)
[/TD]
[TD="align: center"]Group B Item(s)
[/TD]
[TD="align: center"]Group D Item(s)
[/TD]
[TD="align: center"]Top Recurring Item
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]Apple
Orange
Taco
[/TD]
[TD="align: center"]Cheese
[/TD]
[TD="align: center"]Apple
Mayo
Ham
[/TD]
[TD="align: center"]Apple
[/TD]
[TD="align: center"]<--This is an example of ONE obvious result...since all items are listed only once, except Apple (which appears twice)
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]Apple
Beef
Cheese
Soda
[/TD]
[TD="align: center"]Soda
Plum
Cups
[/TD]
[TD="align: center"]Apple
Soda
Chili
[/TD]
[TD="align: center"]Soda
[/TD]
[TD="align: center"]<--Although Soda appears 3 times, and Apple appears 2 times, Soda would be outputted since 3>2
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Orange
Chicken
Flour
[/TD]
[TD="align: center"]Spam
[/TD]
[TD="align: center"]Mayo
Parsely
[/TD]
[TD="align: center"]No Recurring Items
[/TD]
[TD="align: center"]<--Since there aren't any recurring items in these cells, the result is just text stating the obvious
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Tomato
Flour
Mustard
Fries
Bread
[/TD]
[TD="align: center"]Taco
Turkey
Flour
[/TD]
[TD="align: center"]Bread
Corn
[/TD]
[TD="align: center"]Bread
Flour
[/TD]
[TD="align: center"]<--This is an example of a tie. Bread appears twice, as does Flour. As a result of a tie, both Bread and Flour are outputted.
[/TD]
[/TR]
</tbody>[/TABLE]

Is this even possible?
Thanks in advance for your help!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Try this UDF

The code assumes each data within the cell is separated by Alt + Enter (char 10), as shown in your example.

Code: 
Function [COLOR=#0000ff]TopRecurringItem[/COLOR](r As Range)
  Dim c, d, dict As Object, ky, wmax As Long, cad As String
  Set dict = CreateObject("scripting.dictionary")
  For Each c In r
    For Each d In Split(c, Chr(10))
      If Not dict.exists(d) Then
        dict(d) = 1
      Else
        dict(d) = Val(dict(d)) + 1
        If dict(d) > wmax Then wmax = dict(d)
      End If
    Next
  Next
  For Each ky In dict.keys
    If dict(ky) = wmax Then cad = cad & ky & Chr(10)
  Next
  TopRecurringItem = cad
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use TopRecurringItem just like it was a built-in Excel function. For example,

=TopRecurringItem(A2:C2)
 
Last edited:
Upvote 0
Thanks for your help!
That works perfectly (given the example I provided above).
In my actual data (not actually using random items or food) there are occasionally some cells that have error (#VALUE!) cells due to various other factors.
These cells create a problem. Is there any way to work around these?
Like so (see the red and green examples):

Right now it looks/functions like this:
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Group A Item(s)
[/TD]
[TD="align: center"]Group B Item(s)
[/TD]
[TD="align: center"]Group D Item(s)
[/TD]
[TD="align: center"]Top Recurring Item
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]Apple
Orange
Taco[/TD]
[TD="align: center"]Cheese[/TD]
[TD="align: center"]Apple
Mayo
Ham[/TD]
[TD="align: center"]Apple[/TD]
[TD="align: center"]<--This is an example of ONE obvious result...since all items are listed only once, except Apple (which appears twice)
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]Apple
Beef
Cheese
Soda[/TD]
[TD="align: center"]Soda
Plum
Cups[/TD]
[TD="align: center"]Apple
Soda
Chili[/TD]
[TD="align: center"]Soda[/TD]
[TD="align: center"]<--Although Soda appears 3 times, and Apple appears 2 times, Soda would be outputted since 3>2
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Orange
Chicken
Flour[/TD]
[TD="align: center"]Spam[/TD]
[TD="align: center"]Mayo
Parsely[/TD]
[TD="align: center"]No Recurring Items[/TD]
[TD="align: center"]<--Since there aren't any recurring items in these cells, the result is just text stating the obvious
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Tomato
Flour
Mustard
Fries
Bread[/TD]
[TD="align: center"]Taco
Turkey
Flour[/TD]
[TD="align: center"]Bread
Corn[/TD]
[TD="align: center"]Bread
Flour[/TD]
[TD="align: center"]<--This is an example of a tie. Bread appears twice, as does Flour. As a result of a tie, both Bread and Flour are outputted.
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Bread
Mayo
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD="align: center"]Bread
Cheese
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD="align: center"]Spam
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

I would like it to function like this:
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Group A Item(s)
[/TD]
[TD="align: center"]Group B Item(s)
[/TD]
[TD="align: center"]Group D Item(s)
[/TD]
[TD="align: center"]Top Recurring Item
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]Apple
Orange
Taco[/TD]
[TD="align: center"]Cheese[/TD]
[TD="align: center"]Apple
Mayo
Ham[/TD]
[TD="align: center"]Apple[/TD]
[TD="align: center"]<--This is an example of ONE obvious result...since all items are listed only once, except Apple (which appears twice)
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]Apple
Beef
Cheese
Soda[/TD]
[TD="align: center"]Soda
Plum
Cups[/TD]
[TD="align: center"]Apple
Soda
Chili[/TD]
[TD="align: center"]Soda[/TD]
[TD="align: center"]<--Although Soda appears 3 times, and Apple appears 2 times, Soda would be outputted since 3>2
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Orange
Chicken
Flour[/TD]
[TD="align: center"]Spam[/TD]
[TD="align: center"]Mayo
Parsely[/TD]
[TD="align: center"]No Recurring Items[/TD]
[TD="align: center"]<--Since there aren't any recurring items in these cells, the result is just text stating the obvious
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Tomato
Flour
Mustard
Fries
Bread[/TD]
[TD="align: center"]Taco
Turkey
Flour[/TD]
[TD="align: center"]Bread
Corn[/TD]
[TD="align: center"]Bread
Flour[/TD]
[TD="align: center"]<--This is an example of a tie. Bread appears twice, as does Flour. As a result of a tie, both Bread and Flour are outputted.
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Bread
Mayo
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD="align: center"]Bread
Cheese
[/TD]
[TD="align: center"]Bread
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD="align: center"]Spam
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD="align: center"]Spam
[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Basically, if it is one of those weird errored cells, is there any way to simply ignore them in the column C formula?

Thanks for your help!
 
Last edited:
Upvote 0
Try this

Code: 
Function TopRecurringItem(r As Range)
  Dim c, d, dict As Object, ky, wmax As Long, cad As String
  Set dict = CreateObject("scripting.dictionary")
  For Each c In r
    If Not IsError(c) Then
      For Each d In Split(c, Chr(10))
        If Not dict.exists(d) Then
          dict(d) = 1
        Else
          dict(d) = Val(dict(d)) + 1
          If dict(d) > wmax Then wmax = dict(d)
        End If
      Next
    End If
  Next
  For Each ky In dict.keys
    If dict(ky) = wmax Then cad = cad & ky & Chr(10)
  Next
  TopRecurringItem = cad
End Function
 
Upvote 0
default_name said:
That works perfectly (given the example I provided above).
Click to expand...
With two provisos ..
- You don't mind not having the "stating the obvious" text you seemed to want in row 4, and
- You don't mind having an extra carriage return at the end of the result cell. It may be possible this could cause a problem if using these results elsewhere in code or formula.

I also assume that the last result in your table at the bottom of post 3 is a mistake. Otherwise I don't understand why row 4 has no recurring items but row 7 gets one even though Spam doesn't recur.

My suggestion is somewhat similar to Dante's but addresses the points made above & 'compacts' some of the dictionary manipulation code lines.
It does assume that you have the TEXTJOIN function in your Excel version.

Code: 
Function Most(r As Range) As String
  Dim d As Object
  Dim itm As Variant
  Dim MaxCount As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  For Each itm In Split(Evaluate(Replace("textjoin(char(10),1,if(istext(#),#,""""))", "#", r.Address)), Chr(10))
    If Not IsError(itm) Then
      d(itm) = d(itm) + 1
      If d(itm) > MaxCount Then MaxCount = MaxCount + 1
    End If
  Next itm
  If MaxCount < 2 Then
    Most = "No Recurring Items"
  Else
    For Each itm In d.Keys
      If d(itm) = MaxCount Then Most = Most & Chr(10) & itm
    Next itm
    Most = Mid(Most, 2)
  End If
End Function

Excel Workbook
ABCD
1Group A Item(s)Group B Item(s)Group D Item(s)Top Recurring Item
2AppleOrangeTacoCheeseAppleMayoHamApple
3AppleBeefCheeseSodaSodaPlumCupsAppleSodaChiliSoda
4OrangeChickenFlourSpamMayoParselyNo Recurring Items
5TomatoFlourMustardFriesBreadTacoTurkeyFlourBreadCornFlourBread
6BreadMayo#VALUE!BreadCheeseBread
7MayoCheeseNo Recurring Items
8#VALUE!Spam#VALUE!No Recurring Items
Most
 
Last edited:
Upvote 0
Thanks guys!
Dante, thanks for the quick response. It works well!

Peter, you are very right. I should've rephrased my initial post. Although the last result only has one occurrence (no recurrence), I was hoping for just that one (non-error) occurrence to output.
Thank you for adding in the "No Recurring Items" text. I will try and find a way to mix that in with Dante's code.
I also dont have TextJoin in my Excel version, so I couldn't test out your code.

I really appreciate your guys' time and help! It is very much appreciated! You're awesome!
 
Upvote 0
default_name said:
Dante, thanks for the quick response. It works well!

adding in the "No Recurring Items" text. I will try and find a way to mix that in with Dante's code.
Click to expand...

Hi @default_name , Use the following to add the text.

Code: 
Function TopRecurringItem(r As Range)
  Dim c, d, dict As Object, ky, wmax As Long, cad As String
  Set dict = CreateObject("scripting.dictionary")
  For Each c In r
    If Not IsError(c) Then
      For Each d In Split(c, Chr(10))
        If Not dict.exists(d) Then
          dict(d) = 1
        Else
          dict(d) = Val(dict(d)) + 1
          If dict(d) > wmax Then wmax = dict(d)
        End If
      Next
    End If
  Next
  For Each ky In dict.keys
    If dict(ky) = wmax Then cad = cad & ky & Chr(10)
  Next
  TopRecurringItem = [COLOR=#0000ff]IIf(cad = "", "No Recurring Items", cad)[/COLOR]
End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Formula to find Max number of items in orders
Replies
1
Views
680
fjns
F
N
  • Solved
Separate One Cell Data into Multiple Rows
Replies
6
Views
1K
Fluff
Fluff
S
  • Question Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
1K
dmt32
D
bluefeather8989
  • Solved
vba Find matching value from one sheet to another
Replies
4
Views
2K
mumps
mumps

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top