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

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$14=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

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

Herbert,

I'm assuming that you don't know what the unique values are going to be to
start with. So this figures it all out for you. Make sure that the top-left
most cell in your range is selected when you run this. If there is a tie in
the count, the first value is returned. Change the last two
activecell.offset = lines to determine where to place the result set.

Public Sub test()

Dim i As Integer
Dim holder1()
Dim holder2()
Dim bfound As Boolean
Dim intRows As Integer

ReDim holder1(3, 0)
ReDim holder2(3, 0)

Do While ActiveCell.Offset(i, 0) < ""
bfound = False
n = 0

Do Until bfound = True Or n UBound(holder1, 2)
If ActiveCell.Offset(i, 0) = holder1(1, n) And ActiveCell.Offset(i,
1) = holder1(2, n) Then
holder1(3, n) = holder1(3, n) + 1
bfound = True
End If
n = n + 1
Loop

If bfound < True Then
ReDim Preserve holder1(3, UBound(holder1, 2) + 1)
holder1(1, UBound(holder1, 2)) = ActiveCell.Offset(i, 0)
holder1(2, UBound(holder1, 2)) = ActiveCell.Offset(i, 1)
holder1(3, UBound(holder1, 2)) = 1
End If

i = i + 1
Loop

For i = 1 To UBound(holder1, 2)
bfound = False
n = 0
Do Until bfound = True Or n UBound(holder2, 2)
If holder1(1, i) = holder2(1, n) And holder1(3, i) holder2(3, n)
Then
holder2(2, n) = holder1(2, i)
holder2(3, n) = holder1(3, i)
bfound = True
ElseIf holder1(1, i) = holder2(1, n) Then
bfound = True
End If

n = n + 1
Loop

If bfound < True Then
ReDim Preserve holder2(3, UBound(holder2, 2) + 1)
holder2(1, UBound(holder2, 2)) = holder1(1, i)
holder2(2, UBound(holder2, 2)) = holder1(2, i)
holder2(3, UBound(holder2, 2)) = holder1(3, i)
End If

Next i

For i = 1 To UBound(holder2, 2)
ActiveCell.Offset(i - 1, 3) = holder2(1, i)
ActiveCell.Offset(i - 1, 4) = holder2(2, i)
Next i

End Sub
--
Chris Farkas
Excel/Access Developer
<a href="http://www.eAlchemy.biz"www.eAlchemy.biz</a

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

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" ¦b¶l¥ó
¤¤¼¶¼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" ¦b¶l¥ó
¤¤¼¶¼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" ...
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" ¦b¶l¥ó
¤¤¼¶¼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