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"
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 , 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
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 ,
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
wrote:

In article ,
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