Jan 2007 Challenge of the Month Discussion

[MrExcel]

The January 2007 challenge of the month is to find a better way to create stem and leaf charts in Excel. See the original problem at http://www.mrexcel.com/challenge.shtml

 

[Oaktree]

Hi, Bill!

I see you're looking for the "best non-VBA solution", but does "non-VBA" also exclude using the morefunc.dll add-in everyone should have?

Seems that something like

H2: {=SUBSTITUTE(MCONCAT(SMALL(IF(INT($A$2:$E$6/10)=G2,MOD($A$2:$E$6,10)),ROW(INDIRECT("1:"&SUMPRODUCT(--(INT($A$2:$E$6/10)=G2)))))),"FALSE","")} copied down would work...

 

[MrExcel]

I would say using MoreFunc.dll is valid, although if someone does it without MoreFunc.dll, then this would be more elegant...

 

[northwolves]

H2=REPT(0,COUNTIF(A$8:A$32,G2*10)) &SUBSTITUTE( SUMPRODUCT(ROW($1:$9)*(10^COUNTIF(A$8:A$32,G2*10+ROW($1:$9))-1)*10^(COUNTIF(A$8:A$32,">"& G2*10+ROW($1:$9))-COUNTIF(A$8:A$32,">"& G2*10+10))/9),0,"")

Drop it down to H6

 

[northwolves]

Another:
H2=REPT(0,COUNTIF(A$8:A$32,G2*10)) & SUMPRODUCT(ROW($1:$9)*(10^COUNTIF(A$8:A$32,G2*10+ROW($1:$9))-1)*10^(COUNTIF(A$8:A$32,">"&G2*10+ROW($1:$9))+MATCH(G2*10+9,A$8:A$32)-26)/9)

Drop it down to H6

 

[Trances]

Where can i find a list of past Challenges and submissions

 

[xld]

Here is a asolution that works on the original grid. It does put the results into separate cells, so it has to be copied across however many cells that there are in the grid (a use for Excel 2007?)

=IF(ISERROR(INT(SMALL(IF($A$2:$E$6-MOD($A$2:$E$6,10)=$G2*10,MOD($A$2:$E$6,10)*10^5+ROW($A$2:$E$6)*10^2+COLUMN($A$2:$E$6),""),COLUMN(A$1))/10^5)),"",
INT(SMALL(IF($A$2:$E$6-MOD($A$2:$E$6,10)=$G2*10,MOD($A$2:$E$6,10)*10^5+ROW($A$2:$E$6)*10^2+COLUMN($A$2:$E$6),""),COLUMN(A$1))/10^5))

of course it is an array formula.

 

[xld]

It can be reduced to

=INT(SMALL(IF($A$2:$E$6-MOD($A$2:$E$6,10)=$G2*10,MOD($A$2:$E$6,10)*10^5+ROW($A$2:$E$6)*10^2+COLUMN($A$2:$E$6),""),COLUMN(A$1))/10^5)

and use conditional formatting to hide the errors.

 

[markyc]

Hi, Bill!

I see you're looking for the "best non-VBA solution", but does "non-VBA" also exclude using the morefunc.dll add-in everyone should have?

Seems that something like

H2: {=SUBSTITUTE(MCONCAT(SMALL(IF(INT($A$2:$E$6/10)=G2,MOD($A$2:$E$6,10)),ROW(INDIRECT("1:"&SUMPRODUCT(--(INT($A$2:$E$6/10)=G2)))))),"FALSE","")} copied down would work...

Furthermore using morefunc.dll then this can be rewritten as:

H2: {=MCONCAT(REPT(ROW($1:$10)-1,COUNTIF(A$8:A$32,G2*10+ROW($1:$10)-1)))}

I'm trying to think of a way using pivot tables but no luck thus far.
Regards,
Mark

 

[northwolves]

H2=REPT(0,COUNTIF(A$8:A$32,G2*10))& SUMPRODUCT(ROW($1:$9)*INT(10^COUNTIF(A$8:A$32,G2*10+ROW($1:$9))/9)*10^(COUNTIF(A$8:A$32,">"&G2*10+ROW($1:$9))+MATCH(G2*10+9,A$8:A$32)-25))