how can the necessary information be extracted?

[Herbert Chan]

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

 

[Herbert Seidenberg]

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

 

[Ron Rosenfeld]

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

 

[Tushar Mehta]

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

 

[Ron Rosenfeld]

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

 

[Tushar Mehta]

{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

 

[Ron Rosenfeld]

{snip}
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

 

[Herbert Chan]

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

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

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

 

[Guest]

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

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

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

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

 

[Herbert Chan]

Thanks, that works wonders!

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

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

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


:

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