Hello,

I have some arrays like below:

29 5
26 4
24 4
23 3
24 3
25 5
24 3
23 3
24 3
27 4
27 5
25 2
27 4
26 1

For the above set, the most frequently appearing number for 24 is 3, the
most frequently appearing number for 27 is 4, and so forth. i.e., I want to
extract the most frequently appearing number for each number in the first
column.

This is what I want to get:

23 3
24 3
25 2
26 1
27 4
28 0
29 5

Thanks in advance.

Herbert

Same as above, but shorter formula:
=MODE(IF(List1=D1,List2,""))

On 12 Jan 2007 09:17:57 -0800, "Herbert Seidenberg" <herbds7-(E-Mail Removed)>
wrote:

>Same as above, but shorter formula:
>=MODE(IF(List1=D1,List2,""))

Not quite the same. If there are no duplicate data points, you formula returns
#N/A


Ron's formula returns one of the values which, based on the OP's example, would
seem to be what he wants.
--ron

As impressive as Ron Coderre's formula is, it might behoove you to
(a) figure out how to make Herbert Seidenberg's formula with the inclusion
of the appropriate IF(COUNTIF(...)) clause(s), or
(b) leverage the Top N capability of a PivotTable.

Add labels to the top of each column of data. I picked A and B. Create a
PT (Data | PivotTable and PivotChart Report...) with A as the first row
field, B as the 2nd row field, and 'Count of B' as the data field (drag B to
the Data Field area, then double-click the 'Sum of B' header, and in the
resulting dialog box change Count instead of Sum).

Now, in the PT, double click the A header and set the totals to none.
Double-click the B row field header. In the resulting dialog box, click
Advanced... In the resulting dialog box, enable the 'Top 10 Autoshow'
feature and in the choices for 'Show' select Top 1.

The advantage of the PT is that you don't need to know the contents of
column A and XL does all the "heavy lifting," so to say. The disadvantage
is that it does recalculate automatically.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <(E-Mail Removed)>, (E-Mail Removed) says...
> Hello,
>
> I have some arrays like below:
>
> 29 5
> 26 4
> 24 4
> 23 3
> 24 3
> 25 5
> 24 3
> 23 3
> 24 3
> 27 4
> 27 5
> 25 2
> 27 4
> 26 1
>
> For the above set, the most frequently appearing number for 24 is 3, the
> most frequently appearing number for 27 is 4, and so forth. i.e., I want to
> extract the most frequently appearing number for each number in the first
> column.
>
> This is what I want to get:
>
> 23 3
> 24 3
> 25 2
> 26 1
> 27 4
> 28 0
> 29 5
>
> Thanks in advance.
>
> Herbert
>
>
>
>

On Sat, 13 Jan 2007 08:02:54 -0500, Tushar Mehta
<(E-Mail Removed)> wrote:

>As impressive as Ron Coderre's formula is, it might behoove you to
>(a) figure out how to make Herbert Seidenberg's formula with the inclusion
>of the appropriate IF(COUNTIF(...)) clause(s), or
>(b) leverage the Top N capability of a PivotTable.
>
>Add labels to the top of each column of data. I picked A and B. Create a
>PT (Data | PivotTable and PivotChart Report...) with A as the first row
>field, B as the 2nd row field, and 'Count of B' as the data field (drag B to
>the Data Field area, then double-click the 'Sum of B' header, and in the
>resulting dialog box change Count instead of Sum).
>
>Now, in the PT, double click the A header and set the totals to none.
>Double-click the B row field header. In the resulting dialog box, click
>Advanced... In the resulting dialog box, enable the 'Top 10 Autoshow'
>feature and in the choices for 'Show' select Top 1.
>
>The advantage of the PT is that you don't need to know the contents of
>column A and XL does all the "heavy lifting," so to say. The disadvantage
>is that it does recalculate automatically.

What am I doing wrong?

I tried following your directions, and with this data:

A B
29 5
26 4
24 4
23 3
24 3
25 5
24 3
23 3
24 3
27 4
27 5
25 2
27 4
26 1

I obtained this result:



Count of B
B A Total
3 23 2
24 3
3 Total 5
Grand Total 5



--ron

In article <(E-Mail Removed)>,
(E-Mail Removed) says...
{snip}
>
> What am I doing wrong?
>
> I tried following your directions, and with this data:
>
> A B
> 29 5
> 26 4
> 24 4
> 23 3
> 24 3
> 25 5
> 24 3
> 23 3
> 24 3
> 27 4
> 27 5
> 25 2
> 27 4
> 26 1
>
> I obtained this result:
>
>
>
> Count of B
> B A Total
> 3 23 2
> 24 3
> 3 Total 5
> Grand Total 5
>
>
>
> --ron
>
A should be the first row field, B the second. You should have

Count of B
A B Total
23 3 2
24 3 3
25 2 1
5 1
26 1 1
4 1
27 4 2
29 5 1

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

On Sat, 13 Jan 2007 11:38:13 -0500, Tushar Mehta
<(E-Mail Removed)> wrote:

>In article <(E-Mail Removed)>,
>(E-Mail Removed) says...
>{snip}
>>
>> What am I doing wrong?
>>
>> I tried following your directions, and with this data:
>>
>> A B
>> 29 5
>> 26 4
>> 24 4
>> 23 3
>> 24 3
>> 25 5
>> 24 3
>> 23 3
>> 24 3
>> 27 4
>> 27 5
>> 25 2
>> 27 4
>> 26 1
>>
>> I obtained this result:
>>
>>
>>
>> Count of B
>> B A Total
>> 3 23 2
>> 24 3
>> 3 Total 5
>> Grand Total 5
>>
>>
>>
>> --ron
>>
>A should be the first row field, B the second. You should have
>
>Count of B
>A B Total
>23 3 2
>24 3 3
>25 2 1
> 5 1
>26 1 1
> 4 1
>27 4 2
>29 5 1


That does it.

Thanks,
--ron

Hello,

Thanks for the solution. However, my list will grow, and so I'm trying to
replace all the $A$1:$A$14 by $A:$A, and the same thing for column B. After
I've done that, I've got the #NUM error.

What should I do so that the function will take into account the fact that
the list will grow on a regular basis?

Thanks.

Herbert

"Ron Coderre" <(E-Mail Removed)> ¦b¶l¥ó
news:259CBF45-51B1-4D15-A460-(E-Mail Removed) ¤¤¼¶¼g...
> Try something like this:
>
> With
> A1:B14 containing your posted list
>
> AND
> D1: D7 containing this series: 23, 24, 25, 26, 27, 28, 29
>
> Then....put this ARRAY FORMULA in
> E1:
>
=IF(COUNTIF($A$1:$A$14,D1),INDEX($B$1:$B$14,MATCH(MAX(FREQUENCY(IF($A$1:$A$1
4=D1,$A$1:$A$14+($B$1:$B$14*0.1)),$A$1:$A$14+($B$1:$B$14*0.1))),FREQUENCY(IF
($A$1:$A$14=D1,$A$1:$A$14+($B$1:$B$14*0.1)),$A$1:$A$14+($B$1:$B$14*0.1)),0))
,0)
>
> Copy E1 into E2 and down through E7
>
> Note: For array formulas, hold down [Ctrl] and [Shift] when you press
> [Enter], instead of just pressing [Enter].
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Herbert Chan" wrote:
>
> > Hello,
> >
> > I have some arrays like below:
> >
> > 29 5
> > 26 4
> > 24 4
> > 23 3
> > 24 3
> > 25 5
> > 24 3
> > 23 3
> > 24 3
> > 27 4
> > 27 5
> > 25 2
> > 27 4
> > 26 1
> >
> > For the above set, the most frequently appearing number for 24 is 3, the
> > most frequently appearing number for 27 is 4, and so forth. i.e., I want
to
> > extract the most frequently appearing number for each number in the
first
> > column.
> >
> > This is what I want to get:
> >
> > 23 3
> > 24 3
> > 25 2
> > 26 1
> > 27 4
> > 28 0
> > 29 5
> >
> > Thanks in advance.
> >
> > Herbert
> >
> >
> >
> >

I think the best way to accommodate a variable range situation is to use
Dynamic Range Names (DRN)....

Assuming the name of the worksheet with the values is "MySheet"
Try this:

From the Excel main menu:
<insert><name><define>
Names in workbook: rngMajorID
Refers to: =OFFSET(MySheet!$A$1,0,0,COUNT(MySheet!$A:$A),1)
Click the [Add] button

Names in workbook: rngSubID
Refers to: =OFFSET(MySheet!$B$1,0,0,COUNT(MySheet!$B:$B),1)
Click the [OK] button

Now you have 2 Named Ranges that expand and contract according to the values
referenced in their respective definitions.

For more information on Dynamic Range Names, see Debra Dalgleish's website:
http://www.contextures.com/xlNames01.html#Dynamic

Next we need to adjust the formulas....

Step 1: replace the Col_A references with the rngMajorID range name
Select the formulas
[Ctrl]+H.........the shortcut for <edit><replace>
Replace what: $A$1:$A$14 (or whatever your formula reference for Col_A is)
Replace with: rngMajorID
Click [Replace All]

Step 2: replace the Col_B references with the rngMajorID range name
Replace what: $B$1:$B$14 (or whatever your formula reference for Col_B is)
Replace with: rngSubID
Click [Replace All]

That should do it!

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Herbert Chan" wrote:

> Hello,
>
> Thanks for the solution. However, my list will grow, and so I'm trying to
> replace all the $A$1:$A$14 by $A:$A, and the same thing for column B. After
> I've done that, I've got the #NUM error.
>
> What should I do so that the function will take into account the fact that
> the list will grow on a regular basis?
>
> Thanks.
>
> Herbert
>
> "Ron Coderre" <(E-Mail Removed)> ¦b¶l¥ó
> news:259CBF45-51B1-4D15-A460-(E-Mail Removed) ¤¤¼¶¼g...
> > Try something like this:
> >
> > With
> > A1:B14 containing your posted list
> >
> > AND
> > D1: D7 containing this series: 23, 24, 25, 26, 27, 28, 29
> >
> > Then....put this ARRAY FORMULA in
> > E1:
> >
> =IF(COUNTIF($A$1:$A$14,D1),INDEX($B$1:$B$14,MATCH(MAX(FREQUENCY(IF($A$1:$A$1
> 4=D1,$A$1:$A$14+($B$1:$B$14*0.1)),$A$1:$A$14+($B$1:$B$14*0.1))),FREQUENCY(IF
> ($A$1:$A$14=D1,$A$1:$A$14+($B$1:$B$14*0.1)),$A$1:$A$14+($B$1:$B$14*0.1)),0))
> ,0)
> >
> > Copy E1 into E2 and down through E7
> >
> > Note: For array formulas, hold down [Ctrl] and [Shift] when you press
> > [Enter], instead of just pressing [Enter].
> >
> > Does that help?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "Herbert Chan" wrote:
> >
> > > Hello,
> > >
> > > I have some arrays like below:
> > >
> > > 29 5
> > > 26 4
> > > 24 4
> > > 23 3
> > > 24 3
> > > 25 5
> > > 24 3
> > > 23 3
> > > 24 3
> > > 27 4
> > > 27 5
> > > 25 2
> > > 27 4
> > > 26 1
> > >
> > > For the above set, the most frequently appearing number for 24 is 3, the
> > > most frequently appearing number for 27 is 4, and so forth. i.e., I want
> to
> > > extract the most frequently appearing number for each number in the
> first
> > > column.
> > >
> > > This is what I want to get:
> > >
> > > 23 3
> > > 24 3
> > > 25 2
> > > 26 1
> > > 27 4
> > > 28 0
> > > 29 5
> > >
> > > Thanks in advance.
> > >
> > > Herbert
> > >
> > >
> > >
> > >
>
>
>

Thanks, that works wonders!

Herbert

"Ron Coderre" <(E-Mail Removed)> ¼¶¼g©ó¶l¥ó·s»D:712E7E4F-8700-40F1-BE1D-(E-Mail Removed)...
>I think the best way to accommodate a variable range situation is to use
> Dynamic Range Names (DRN)....
>
> Assuming the name of the worksheet with the values is "MySheet"
> Try this:
>
> From the Excel main menu:
> <insert><name><define>
> Names in workbook: rngMajorID
> Refers to: =OFFSET(MySheet!$A$1,0,0,COUNT(MySheet!$A:$A),1)
> Click the [Add] button
>
> Names in workbook: rngSubID
> Refers to: =OFFSET(MySheet!$B$1,0,0,COUNT(MySheet!$B:$B),1)
> Click the [OK] button
>
> Now you have 2 Named Ranges that expand and contract according to the
> values
> referenced in their respective definitions.
>
> For more information on Dynamic Range Names, see Debra Dalgleish's
> website:
> http://www.contextures.com/xlNames01.html#Dynamic
>
> Next we need to adjust the formulas....
>
> Step 1: replace the Col_A references with the rngMajorID range name
> Select the formulas
> [Ctrl]+H.........the shortcut for <edit><replace>
> Replace what: $A$1:$A$14 (or whatever your formula reference for Col_A is)
> Replace with: rngMajorID
> Click [Replace All]
>
> Step 2: replace the Col_B references with the rngMajorID range name
> Replace what: $B$1:$B$14 (or whatever your formula reference for Col_B is)
> Replace with: rngSubID
> Click [Replace All]
>
> That should do it!
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Herbert Chan" wrote:
>
>> Hello,
>>
>> Thanks for the solution. However, my list will grow, and so I'm trying
>> to
>> replace all the $A$1:$A$14 by $A:$A, and the same thing for column B.
>> After
>> I've done that, I've got the #NUM error.
>>
>> What should I do so that the function will take into account the fact
>> that
>> the list will grow on a regular basis?
>>
>> Thanks.
>>
>> Herbert
>>
>> "Ron Coderre" <(E-Mail Removed)> ¦b¶l¥ó
>> news:259CBF45-51B1-4D15-A460-(E-Mail Removed) ¤¤¼¶¼g...
>> > Try something like this:
>> >
>> > With
>> > A1:B14 containing your posted list
>> >
>> > AND
>> > D1: D7 containing this series: 23, 24, 25, 26, 27, 28, 29
>> >
>> > Then....put this ARRAY FORMULA in
>> > E1:
>> >
>> =IF(COUNTIF($A$1:$A$14,D1),INDEX($B$1:$B$14,MATCH(MAX(FREQUENCY(IF($A$1:$A$1
>> 4=D1,$A$1:$A$14+($B$1:$B$14*0.1)),$A$1:$A$14+($B$1:$B$14*0.1))),FREQUENCY(IF
>> ($A$1:$A$14=D1,$A$1:$A$14+($B$1:$B$14*0.1)),$A$1:$A$14+($B$1:$B$14*0.1)),0))
>> ,0)
>> >
>> > Copy E1 into E2 and down through E7
>> >
>> > Note: For array formulas, hold down [Ctrl] and [Shift] when you press
>> > [Enter], instead of just pressing [Enter].
>> >
>> > Does that help?
>> > ***********
>> > Regards,
>> > Ron
>> >
>> > XL2002, WinXP
>> >
>> >
>> > "Herbert Chan" wrote:
>> >
>> > > Hello,
>> > >
>> > > I have some arrays like below:
>> > >
>> > > 29 5
>> > > 26 4
>> > > 24 4
>> > > 23 3
>> > > 24 3
>> > > 25 5
>> > > 24 3
>> > > 23 3
>> > > 24 3
>> > > 27 4
>> > > 27 5
>> > > 25 2
>> > > 27 4
>> > > 26 1
>> > >
>> > > For the above set, the most frequently appearing number for 24 is 3,
>> > > the
>> > > most frequently appearing number for 27 is 4, and so forth. i.e., I
>> > > want
>> to
>> > > extract the most frequently appearing number for each number in the
>> first
>> > > column.
>> > >
>> > > This is what I want to get:
>> > >
>> > > 23 3
>> > > 24 3
>> > > 25 2
>> > > 26 1
>> > > 27 4
>> > > 28 0
>> > > 29 5
>> > >
>> > > Thanks in advance.
>> > >
>> > > Herbert
>> > >
>> > >
>> > >
>> > >
>>
>>
>>