Frequency report

montecarlo2012 · Oct 13, 2012

Hello everyone.
I am trying to generate a FREQUENCY REPORT but I need the results show up in different way than the regular frequency function, I was trying to do it using =IFERROR(INDEX ....etc. but as you know I think is longer than a vba code, and beside do not work in my case, so the images show exactly what I need.

Code:

[TABLE="width: 528"]
<tbody>[TR]
[TD="class: xl63, width: 21, bgcolor: #FFC000"][FONT=Calibri]B[/FONT]
[/TD]
[TD="class: xl63, width: 21, bgcolor: #FFC000"][FONT=Calibri][SIZE=3][COLOR=#000000]C[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 21, bgcolor: #FFC000"][FONT=Calibri][SIZE=3][COLOR=#000000]D[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 21, bgcolor: #FFC000"][FONT=Calibri][SIZE=3][COLOR=#000000]E[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 21, bgcolor: #FFC000"][FONT=Calibri][SIZE=3][COLOR=#000000]F[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 21, bgcolor: #FFC000"][FONT=Calibri][SIZE=3][COLOR=#000000]G[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 64, bgcolor: #FFC000"][FONT=Calibri][SIZE=3][COLOR=#000000]H[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 64, bgcolor: #FFC000"][FONT=Calibri][SIZE=3][COLOR=#000000]I[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 64, bgcolor: #FFC000"][FONT=Calibri][SIZE=3][COLOR=#000000]J[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 64, bgcolor: #FFC000"][FONT=Calibri][SIZE=3][COLOR=#000000]K[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 64, bgcolor: #FFC000"][FONT=Calibri][SIZE=3][COLOR=#000000]L[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 64, bgcolor: #FFC000"][FONT=Calibri][SIZE=3][COLOR=#000000]M[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 64, bgcolor: #FFC000"][FONT=Calibri][SIZE=3][COLOR=#000000]N[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 64, bgcolor: #FFC000"][FONT=Calibri][SIZE=3][COLOR=#000000]O[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl63, width: 64, bgcolor: #FFC000"][FONT=Calibri][SIZE=3][COLOR=#000000]P[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]11[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]19[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]29[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]31[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]37[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]50[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: yellow"][FONT=Calibri][SIZE=3][COLOR=#000000]0 times[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: yellow"][FONT=Calibri][SIZE=3][COLOR=#000000]1 time[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: yellow"][FONT=Calibri][SIZE=3][COLOR=#000000]2 times[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: yellow"][FONT=Calibri][SIZE=3][COLOR=#000000]3 times[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: yellow"][FONT=Calibri][SIZE=3][COLOR=#000000]4 times[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: yellow"][FONT=Calibri][SIZE=3][COLOR=#000000]5 times[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl65, bgcolor: yellow"][SIZE=3][COLOR=#000000][FONT=Calibri]6 times [/FONT][/COLOR][/SIZE]
[/TD]
[TD="class: xl65, bgcolor: yellow"][FONT=Calibri][SIZE=3][COLOR=#000000]7 times[/COLOR][/SIZE][/FONT]
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]5[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]12[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]19[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]30[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]41[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]48[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]9[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]4[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]2[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]1[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]5[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]19[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]13[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]14[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]25[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]26[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]37[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]39[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]17[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]6[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]3[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]7[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]21[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]10[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]15[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]21[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]28[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]35[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]41[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]20[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]8[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]10[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]12[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]27[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]1[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]3[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]16[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]19[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]25[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]38[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]34[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]15[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]11[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]13[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]41[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]13[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]19[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]30[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]43[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]46[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]51[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]40[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]16[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]24[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]14[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]5[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]7[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]12[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]22[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]31[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]53[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]18[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]25[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]23[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]5[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]12[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]21[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]24[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]51[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]53[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]22[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]26[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]28[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]2[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]6[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]7[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]11[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]21[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]41[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]32[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]46[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]29[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]14[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]21[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]27[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]31[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]45[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]52[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]33[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]48[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]30[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]23[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]28[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]29[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]36[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]37[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]46[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]35[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]51[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]31[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]2[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]8[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]23[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]27[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]43[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]53[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]38[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]36[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]1[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]28[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]36[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]39[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]44[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]47[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]42[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]37[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]1[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]18[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]23[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]27[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]33[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]36[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]44[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]39[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]14[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]29[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]30[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]41[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]43[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]49[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]49[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]43[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]4[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]26[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]39[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]42[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]45[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]47[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]50[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]45[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]3[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]7[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]10[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]19[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]24[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]27[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]52[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]47[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri]5[/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]13[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]32[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]45[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]47[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]48[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent, align: right"][FONT=Calibri][SIZE=3][COLOR=#000000]53[/COLOR][/SIZE][/FONT]
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

this is base on a range of 18 rows but I would like to enter the range I need in different cases, I mean I have to do this many times in different ways.
Any help welcome.

jim may · Oct 13, 2012

Set MyRange = Selection (That which is currently Selected/Highlighted)... (maybe)...

montecarlo2012 · Oct 13, 2012

Thanks to read this.
But I afraid NOT.
I Know a VBA code is the answer I already tryed formulas and nothing.

Aladin Akyurek · Oct 13, 2012

montecarlo2012 said:
Thanks to read this.
But I afraid NOT.
I Know a VBA code is the answer I already tryed formulas and nothing.

Which version of Excel are you working with?

MickG · Oct 13, 2012

Try this for you current range, if it works I'll alter it for a greater range.
Results Sheet(2) "A1"
Your Data in Range "B1 To G18"

Code:

[COLOR=navy]Sub[/COLOR] MG13Oct39
[COLOR=navy]Dim[/COLOR] oMax        [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Rng         [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn          [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] nMax        [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Ac          [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Rw          [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] c           [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
ReDim nRay(1 To 54, 1 To 54)
[COLOR=navy]Set[/COLOR] Rng = Range("B1:G18")
    oMax = application.Max(Rng)
        ReDim Ray(1 To oMax, 1 To 2)
[COLOR=navy]For[/COLOR] n = 1 To oMax
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        Ray(n, 1) = n
            [COLOR=navy]If[/COLOR] Dn = n [COLOR=navy]Then[/COLOR] Ray(n, 2) = Ray(n, 2) + 1
    [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]Next[/COLOR] n
[COLOR=navy]For[/COLOR] Ac = 0 To 53
    c = 1
    nRay(1, Ac + 1) = Ac
        [COLOR=navy]For[/COLOR] Rw = 1 To 53
            nMax = application.Max(Ray(Rw, 2), nMax)
            Ray(Rw, 2) = IIf(Ray(Rw, 2) = "", 0, Ray(Rw, 2))
            [COLOR=navy]If[/COLOR] Ray(Rw, 2) = Ac [COLOR=navy]Then[/COLOR]
                c = c + 1
                nRay(c, Ac + 1) = Ray(Rw, 1)
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] Rw
 [COLOR=navy]Next[/COLOR] Ac
Sheets("Sheet2").Range("A1").Resize(53, nMax + 1) = nRay
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

montecarlo2012 · Oct 13, 2012

Thank you MickG.
Yes the results shows up in sheet 2.

Aladin Akyurek, thanks for reading, for your question I am using ms excel 2010.

MickG · Oct 13, 2012

Extra rows should be no problem.
I can place the results starting column "I"
Will you data always be in columns "B to G" or will you use more columns.

montecarlo2012 · Oct 13, 2012

I really appreciate your work MickG, is great. I already change from sheet 2 to one and result start in J. I received this email exactly when I was thinking in other question.
MickG thank you so much.

Aladin Akyurek · Oct 13, 2012

montecarlo2012 said:
...

Aladin Akyurek, thanks for reading, for your question I am using ms excel 2010.

<form class="block vbform" onsubmit="return vB_Editor['vB_Editor_001'].prepare_submit(0, 1)" method="post" name="vbform" action="newreply.php?do=postreply&t=664235">11 </form>	19	29	31	37	50	0	1	2	3	4	5	6	7
5	12	19	30	41	48	9	4	2	1	5	19
13	14	25	26	37	39	17	6	3	7	21
10	15	21	28	35	41	20	8	10	12	27
1	3	16	19	25	38	34	15	11	13	41
13	19	30	43	46	51	40	16	24	14
5	7	12	22	31	53		18	25	23
5	12	21	24	51	53		22	26	28
2	6	7	11	21	41		32	46	29
14	21	27	31	45	52		33	48	30
23	28	29	36	37	46		35	51	31
2	8	23	27	43	53		38		36
1	28	36	39	44	47		42		37
1	18	23	27	33	36		44		39
14	29	30	41	43	49		49		43
4	26	39	42	45	47		50		45
3	7	10	19	24	27		52		47
5	13	32	45	47	48				53

B1:G18 on Sheet1 houses the data of interest.

Define BinVec using Formulas | Name Manager as referring to:

Rich (BB code):

=ROW(INDIRECT(MIN(Sheet1!$B$1:$G$18)&":"&MAX(Sheet1!$B$1:$G$18)))

I2, control+shift+enter, not just enter, and copy down:

Rich (BB code):

=IFERROR(
  SMALL(IF(ISNUMBER(IF(FREQUENCY($B$1:$G$18,BinVec),FALSE,BinVec)),
    IF(FREQUENCY($B$1:$G$18,BinVec),FALSE,BinVec)),ROWS(I$2:I2)),"")

J2, control+shift+enter, copy across, and down:

Rich (BB code):

=IFERROR(SMALL(IF(FREQUENCY($B$1:$G$18,BinVec)=J$1,BinVec),ROWS(J$2:J2)),"")

montecarlo2012 · Oct 16, 2012

Thanks MickG.
I was thinking about what you said "I'll alter it for a greater range"; and what I realize is I really need a little variation, let me tell you.
In your code the range is B1:G18 thats fine according to my first statement;
now come the variation: change the range to B2:G19 but highlight the numbers in B1:G1 into the report.
In my example at the beginning the table start with 11-19-29-31-37-50 that number is in B1:G1, I would appreciate the code take the frequency report from B2:G19 means start with the row B2:G2 (5-12-19-30-41-48), of course the numbers in B1:G1 they gonna be Just highlight .

Frequency report

montecarlo2012

Well-known Member

Excel Facts

jim may

Well-known Member

montecarlo2012

Well-known Member

Aladin Akyurek

MrExcel MVP

MickG

MrExcel MVP

montecarlo2012

Well-known Member

MickG

MrExcel MVP

montecarlo2012

Well-known Member

Aladin Akyurek

MrExcel MVP

montecarlo2012

Well-known Member

Similar threads

Forum statistics

Share this page

Frequency report

Well-known Member

Excel Facts

Well-known Member

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock