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

[JayHero]

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)

 

[inactiveuserps07]

Re: Help with returning adjacent text cell for the maximum occurrence within groups of IDs

@JayHero, see if this works for you (assuming your headers are in Row 1 and your data starts in Row 2):

=INDEX(B$2:B$18,SUMPRODUCT(MAX((A$2:A$18=A2)*(E$2:E$18=H2)*(ROW(A$2:A$18)-1))))

I used SUMPRODUCT() to avoid needing to enter as an array formula.

 

[Peter_SSs]

Re: Help with returning adjacent text cell for the maximum occurrence within groups of IDs

@JayHero

Could you confirm/explain what the expected result(s) would be for this sample data?

Book1
	A	B
1	ID (A)	CODE (B)
2	1	A
3	1	B
4	1	C
5	1	B
6	1	C
7	1	D
8	1	A
9
Example

 

[JayHero]

Re: Help with returning adjacent text cell for the maximum occurrence within groups of IDs

Thanks Erik it work perfectly. I did try variations using "sumproduct' and "row" but could not get those to work quite right too.

 

[JayHero]

Re: Help with returning adjacent text cell for the maximum occurrence within groups of IDs

Peter, Erik's solution above worked for me. However, if you have a solution that returns the text that occurs the most frequent per Id without using the extra percent column i was using to do this, that would be great too.

[TABLE="width: 202"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ID CODE Results
1 A A
1 B A
1 A A
1 A A
1 A A
1 B A
2 A B
2 B B
2 B B
2 C B
2 D B
3 A C
3 B C
3 C C
3 C C
3 E C
3 D C
4 B B
4 A B
4 B B
4 B B
4 A B
4 B B


[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[inactiveuserps07]

Re: Help with returning adjacent text cell for the maximum occurrence within groups of IDs

@JayHero, if you want to avoid using helper Column H, you can use the following array formula:

=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)))

Just be sure to use Ctrl+Shift+Enter.

 

[Peter_SSs]

Re: Help with returning adjacent text cell for the maximum occurrence within groups of IDs

Peter, Erik's solution above worked for me. However, if you have a solution that returns the text that occurs the most frequent per Id without using the extra percent column i was using to do this, that would be great too.

I think you missed the point of my question, or else I have misunderstood your question.
In my example, there are three codes that all have equal maximum occurrence within ID 1.
I was just questioning why, of if, you only want one of them returned in the 'Results Needed' column?

 

[JayHero]

Re: Help with returning adjacent text cell for the maximum occurrence within groups of IDs

Peter I am follow you now after rereading your first post. I did consider this initially and did a quick scan of my data and found 1 or 2 such instances. I will do a more thorough check but the chances of this occurring due to the nature of the items in my data is low and i am willing to accept a few areas where I will need to dig down into it more. The data I pulled for this is not large only about 8000 rows. There will be no more than 12 repeated Id occurrences and there are only about 10 codes total. Also, there should be no more the 4-5 unique codes per Id with the majority being 2-3 unique codes per Id. I built-in additional COUNTIF, COUNTIFS, SUMIF and percent occurrences columns to help me filter down to take a closer look. I appreciate your insight on this and any additional suggestions will also be appreciated.

 

[Peter_SSs]

Re: Help with returning adjacent text cell for the maximum occurrence within groups of IDs

.. any additional suggestions will also be appreciated.

See if this is any use. It uses just columns A, B and D but does require Excel 16 through Office 365.
D2 is copied down
I2 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	D	I
1	ID (A)	CODE (B)	Code Occurrence (D)	Codes with Max Occur.
2	1	A	2	A, C, D
3	1	B	1	A, C, D
4	1	C	2	A, C, D
5	1	D	2	A, C, D
6	1	C	2	A, C, D
7	1	D	2	A, C, D
8	1	A	2	A, C, D
9	2	A	1	B
10	2	B	2	B
11	2	B	2	B
12	2	C	1	B
13	2	D	1	B
14	3	A	1	C
15	3	B	1	C
16	3	C	2	C
17	3	C	2	C
18	3	E	1	C
19	3	D	1	C
Example

 

[Peter_SSs]

Re: Help with returning adjacent text cell for the maximum occurrence within groups of IDs

If you don't have the appropriate Excel version with TEXTJOIN and MAXIFS, then perhaps this array formula, still only using columns A, B & D may be of use.

Excel Workbook
	A	B	D	I
1	ID (A)	CODE (B)	Code Occurrence (D)	Codes with Max Occur.
2	1	A	3	Multiple
3	1	B	1	Multiple
4	1	C	3	Multiple
5	1	C	3	Multiple
6	1	C	3	Multiple
7	1	A	3	Multiple
8	1	A	3	Multiple
9	2	A	1	B
10	2	B	2	B
11	2	B	2	B
12	2	C	1	B
13	2	D	1	B
14	3	A	1	C
15	3	B	1	C
16	3	C	2	C
17	3	C	2	C
18	3	E	1	C
19	3	D	1	C
Example (2)

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.