Find the mode of column B for each unique number in column A

[mrpolyglot]

Hi all,

I am new to this forum, and really need help with the following task:

1) I have a list of of random numbers in column A
2) I have a second list of random numbers in column B
3) Would like to find the mode of column B for each unique number in column A

For example:

Column A, Column B
1, 2
1, 18
2, 7
18, 9
5, 17
1, 2
18, 4
5, 17
2, 3
2, 7
1, 2

Is there a formula that could find the mode of column B for each unique number in column A, and place each unique number (sorted) in column C and the mode in column D:

Column C, Column D
1, 2
2, 7
5, 17
18, N/A

I know how to manually do this, but cannot for the life of me perform the same via a formula in Excel. Thank you in advance for your help. It is greatly appreciated.

 

[Aladin Akyurek]

[TABLE="width: 259"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2929" width=82><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4778" width=134><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]X[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Y[/TD]
[TD="class: xl64, width: 82, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl64, width: 134, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent"]X Unique[/TD]
[TD="class: xl63, bgcolor: transparent"]Mode Y while X[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]18[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]17[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]5[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl64, bgcolor: transparent, align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]18[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]5[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

C1:

=SUM(IF(FREQUENCY(A2:A12,A2:A12),1))

C3, control+shift+enter (CSE), not just enter, and copy down:

=IF(ROWS($C$3:C3)<=$C$1,MIN(IF(ISNUMBER(MATCH($A$2:$A$12,$C$2:C2,0)),"#",
  IF(ISNUMBER($A$2:$A$12),$A$2:$A$12,"#"))),"")

D3, CSE and copy down:

=IF($C3="","",MODE(IF($A$2:$A$12=$C3,$B$2:$B$12)))

 

[mrpolyglot]

Thank you so much for your help. Your logic did the trick.

 

[Aladin Akyurek]

Thank you so much for your help. Your logic did the trick.

You are welcome. Thanks for providing feedback.