ExcelBanter - View Single Post - how can the necessary information be extracted?

Herbert Chan

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