Subgroup and list top 2

[Danny54]

How can I go about creating a subgroup listing of the top 2

The data

Name
a
a
b
b
b
c
c
d
d
d
d


output to starting range("G15")

Top 2
D = 4
B = 3


Even the simple stuff sometimes seams difficult

 

[Eric W]

One option:

	A	G
Name
a
a
b
b
b
c
c
d
d
d
d
x
Top 2
d
b
a
c
x

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G16[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$20,MODE(IFERROR(MATCH($A$2:$A$20,$A$2:$A$20,0)*{1,1}/(COUNTIF($G$15:$G15,$A$2:$A$20)=0),""))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Danny54]

Thanks for the quick help. I did a control c on the web page taking everything between the {} and went to my excel sheet. I set the cursor on column G16. I pasted the code into the excel window at the top and did the control+shift+Enter as you suggested. My formula now has the brackets around it and returned a d in G16. which is correct. How do I get the b in G17 which would be the second highest value and the number of times found

d = 4
b = 3

 

[Danny54]

how do i get the second highest in the next row as well as having the number of times each one is found?

something like this

Name Found

D 4
B 3

 

[Eric W]

To get the second highest, just copy the formula in G16 to G17. (Control-C / Control-V, or just drag the cell down.) You can copy it down the column as far as you want. To get the count, enter this formula in H16:

=COUNTIF($A$2:$A$20,G16)

copy that down as far as you want.

 

[Danny54]

Again Thanks...

When I copy the formula down one or two by doing a drag, it shows the same value as the previous cell.

 

[Eric W]

Are you actually putting the formula in G16? This part of the formula:

COUNTIF($G$15:$G15,$A$2:$A$20)=0

checks the cells above to exclude any values that have already been used. If you put the formula somewhere else, say X29, you'd need to change that part to:

COUNTIF($X$28:$X28,$A$2:$A$20)=0


If it is in G16, then when you copied it, did you copy or drag? If you dragged, you selected G16, put the cursor on the tiny square on the bottom right of the cell, held down the left mouse key, and dragged down, then released?

How about the data? Are there values that look the same, but are different because of spaces?

 

[Danny54]

G15 has the value Top 2
G16 has the formula
=IFERROR(INDEX($A$2:$A$20,MODE(IFERROR(MATCH($A$2:$A$20,$A$2:$A$20,0)*{1,1}/(COUNTIF($G$15:$G15,$A$2:$A$20)=0),""))),"") with {} around it

this row return the letter d which is correct

Dragging down or double clicking the next formula looks like this
=IFERROR(INDEX($A$2:$A$20,MODE(IFERROR(MATCH($A$2:$A$20,$A$2:$A$20,0)*{1,1}/(COUNTIF($G$15:$G15,$A$2:$A$20)=0),""))),"") with {} around it as well

this row returns the letter d as well - should be a b.

inserting formula

=COUNTIF($A$2:$A$20,G16) into H16 gives me the count of whats found in G16.


Thanks

 

[Eric W]

G15 has the value Top 2
G16 has the formula
=IFERROR(INDEX($A$2:$A$20,MODE(IFERROR(MATCH($A$2:$A$20,$A$2:$A$20,0)*{1,1}/(COUNTIF($G$15:$G15,$A$2:$A$20)=0),""))),"") with {} around it

this row return the letter d which is correct

Dragging down or double clicking the next formula looks like this
=IFERROR(INDEX($A$2:$A$20,MODE(IFERROR(MATCH($A$2:$A$20,$A$2:$A$20,0)*{1,1}/(COUNTIF($G$15:$G15,$A$2:$A$20)=0),""))),"") with {} around it as well

this row returns the letter d as well - should be a b.

inserting formula

=COUNTIF($A$2:$A$20,G16) into H16 gives me the count of whats found in G16.


Thanks

In G16, the formula should look exactly the same as the formula in G15, EXCEPT for the value I marked in red. In G16 it should be G15, in G17 it should be G16. As you go down the column, that value should increment by 1. Notice how that value does not have a $ in front of it?

 

[Danny54]

Thanks,

this one is really strange on my end. I came in this morning with a fresh mind, redid the first column g16 and h16, applying the formulas as you suggested and got the desired results for those columns.

Now the strange stuff, I dragged using the lower right + sign down to copy the formulas to the next row. It showed the same numbers as in the previous row but looking at the formula in the window, it had incremented the row column by 1 (in the formula) as your last post showed. Being perplexed, i copied the formulas from those columns into a text pad and then pasted them back in using the shift+ctrl+enter key and they both worked. Doing this for each column and row following worked. Something funny with the drag perhaps.

What seems to be happening is the formula really isnt being copied, just the values... I wonder if its a office 365 error of some kind or a options that i've failed to set.