Returning adjacent text cell for the maximum occurrence within groups of IDs

JayHero

New Member
Joined
Jan 21, 2016
Messages
5
Help with returning adjacent text cell for the maximum occurrence within groups of IDs
Data:
[TABLE="width: 605"]
<tbody>[TR]
[TD]ID (A)
[/TD]
[TD]CODE (B)
[/TD]
[TD]SKU Count (C)
[/TD]
[TD]Code Occurrence (D)
[/TD]
[TD]Code % Of Occurrence
(E)
[/TD]
[TD]Max Occurrence % (H)
[/TD]
[TD]Results Needed
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]6
[/TD]
[TD]4
[/TD]
[TD]0.67
[/TD]
[TD]0.67
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]B
[/TD]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]0.33
[/TD]
[TD]0.67
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]6
[/TD]
[TD]4
[/TD]
[TD]0.67
[/TD]
[TD]0.67
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]6
[/TD]
[TD]4
[/TD]
[TD]0.67
[/TD]
[TD]0.67
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[TD]6
[/TD]
[TD]4
[/TD]
[TD]0.67
[/TD]
[TD]0.67
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]B
[/TD]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]0.33
[/TD]
[TD]0.67
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]A
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]0.20
[/TD]
[TD]0.40
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]B
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[TD]0.40
[/TD]
[TD]0.40
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]B
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[TD]0.40
[/TD]
[TD]0.40
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]C
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]0.20
[/TD]
[TD]0.40
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]D
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]0.20
[/TD]
[TD]0.40
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]A
[/TD]
[TD]6
[/TD]
[TD]1
[/TD]
[TD]0.17
[/TD]
[TD]0.33
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]B
[/TD]
[TD]6
[/TD]
[TD]1
[/TD]
[TD]0.17
[/TD]
[TD]0.33
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]C
[/TD]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]0.33
[/TD]
[TD]0.33
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]C
[/TD]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]0.33
[/TD]
[TD]0.33
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]E
[/TD]
[TD]6
[/TD]
[TD]1
[/TD]
[TD]0.17
[/TD]
[TD]0.33
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]D
[/TD]
[TD]6
[/TD]
[TD]1
[/TD]
[TD]0.17
[/TD]
[TD]0.33
[/TD]
[TD]C
[/TD]
[/TR]
</tbody>[/TABLE]

My Formula works for SKU 1 but not for SKU 2 & 3
=INDEX($A$2:$H$18,MATCH(A2,$A$2:$A$18,0)*MATCH(H2,$E$2:$E$18,0),2)
 
Re: Help with returning adjacent text cell for the maximum occurrence within groups of IDs

A third option, that doesn't require any helper columns at all, would be to employ a user-defined function as follows.
I have assumed that the ID column & Code column will be adjacent columns and in that order.

To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Code:
Function MaxCodes(rng_ID_Code As Range, sID As String) As String
  Dim d As Object
  Dim a As Variant, itm As Variant
  Dim i As Long, IDmax As Long
  Dim s As String
  
  Set d = CreateObject("Scripting.Dictionary")
  a = rng_ID_Code.Value
  For i = 1 To UBound(a)
    If a(i, 1) = sID Then
      s = a(i, 1) & "|" & a(i, 2)
      d(s) = d(s) + 1
      If d(s) > IDmax Then IDmax = d(s)
    End If
  Next i
  For Each itm In d.keys
    If d(itm) = IDmax Then MaxCodes = MaxCodes & ", " & Split(itm, "|")(1)
  Next itm
  MaxCodes = Mid(MaxCodes, 3)
End Function

Excel Workbook
ABI
1ID (A)CODE (B)Codes with Max Occur.
21AA, C
31BA, C
41CA, C
51CA, C
61CA, C
71AA, C
81AA, C
92AB
102BB
112BB
122CB
132DB
143AC
153BC
163CC
173CC
183EC
193DC
Example (3)
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Re: Help with returning adjacent text cell for the maximum occurrence within groups of IDs

Re Peter's Note: :warning: A word of warning: If you do continue to use Erik's formula, be aware that if you, or another user, subsequently inserts any new rows at the top of the sheet, the formulas will return incorrect results.

... or you simply adapt the formula to your needs replacing the final 1 with the number of the row before your data starts (so if you start your data on Row 7, the final 1 in the formula would become a 6).

=INDEX(B$2:B$18,MAX((A$2:A$18=A2)*(E$2:E$18=MAX(IF(A$2:A$18=A2,E$2:E$18)))*(ROW(A$2:A$18)-1)))
 
Upvote 0
Re: Help with returning adjacent text cell for the maximum occurrence within groups of IDs

... or you simply adapt the formula to your needs replacing the final 1 with the number of the row before your data starts (so if you start your data on Row 7, the final 1 in the formula would become a 6).

[/COLOR]=INDEX(B$2:B$18,MAX((A$2:A$18=A2)*(E$2:E$18=MAX(IF(A$2:A$18=A2,E$2:E$18)))*(ROW(A$2:A$18)-1)))
That would still mean the user needing to know (& remember) to adjust the formula any time rows were added or deleted. Why not make the formula independent of such changes?

=INDEX(B$2:B$19,MAX((A$2:A$19=A2)*(E$2:E$19=MAX(IF(A$2:A$19=A2,E$2:E$19)))*(ROW(A$2:A$19)-ROW(A$2)+1)))
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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