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]

X	Y	4
1	2	X Unique	Mode Y while X
1	18	1	2
2	7	2	7
18	9	5	17
5	17	18	#N/A
1	2
18	4
5	17
2	3
2	7
1	2

<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>
</TBODY>

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.