Curious question about random selection

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,362
Office Version
  1. 2010
Hi everybody <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I am curious can we chose random number out of numbers if they are in one cell separated by coma<o:p></o:p>
<o:p></o:p>
Example:<o:p></o:p>
<TABLE style="WIDTH: 142pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm" border=0 cellSpacing=0 cellPadding=0 width=189><TBODY><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 60pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=80 noWrap>
*<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 82pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=109 noWrap>
A<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
1<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
11, 12, 14, 18, 19<o:p></o:p>​
</TD></TR></TBODY></TABLE>
<o:p></o:p>
Is there any formula so Random number can be selected from cell A1<o:p></o:p>
<o:p></o:p>
Thanks and Regards<o:p></o:p>
Moti<o:p></o:p>
 
Sort by date Sort by votes
motilulla said:
Hi Peter Sir,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
regarding your post 6, your formula is working perfect, as per my request.<o:p></o:p>
<o:p></o:p>
But now instead of one I want two random numbers. I tried this adding another row to the table and copying down the formula but most of the time i get the Repeated numbers from selected group, as shown in cell B and E.<o:p></o:p>
<o:p></o:p>
<TABLE style="WIDTH: 365pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm" border=0 cellSpacing=0 cellPadding=0 width=487><TBODY><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 27pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=36 noWrap>
*<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 44pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=59 noWrap>
A<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 82pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=109 noWrap>
B<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 82pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=109 noWrap>
C<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=87 noWrap>
D<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=87 noWrap>
E<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
1<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
3, 4, 5, 8<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
11, 12, 14, 18, 19<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
21, 22, 24, 27, 29<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
33, 34, 36, 37<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
42, 44, 45, 48<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
2<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
3<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
11<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
21<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
37<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
48<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
3<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
5<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
11<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
22<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
36<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
48<o:p></o:p>​
</TD></TR></TBODY></TABLE>
<o:p></o:p>
What can i do to get instead of repeated two unique numbers always?<o:p></o:p>
<o:p></o:p>
Please can you check it for me?<o:p></o:p>
<o:p></o:p>
Secondly, thank you facethegod for helping and showing interest towards my post.<o:p></o:p>
<o:p></o:p>
Thanks and regards,<o:p></o:p>
Moti.<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Click to expand...
I'm not sure if that would be possible with standard formulas? I think you would need some vba. I suggest you do a search on the board for something like 'random selection without repeat'.
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi Peter Sir,

I am not sure if i would want to use vba for this. I have an idea which i want you to tell me if it would work. When i use your formula the result i get is the following:

<TABLE style="WIDTH: 383pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=511 x:str><COLGROUP><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 60pt" width=80><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" span=2 width=109><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" span=2 width=87><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; WIDTH: 29pt; HEIGHT: 12.75pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 width=39>*


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 60pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=80>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 82pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=109>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 82pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=109>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 65pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=87>D</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 65pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: #666699 1pt solid" class=xl25 width=87>E</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=17 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>3, 4, 5, 8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>11, 12, 14, 18, 19</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>21, 22, 24, 27, 29</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>33, 34, 36, 37</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #666699 1pt solid" class=xl27>42, 44, 45, 48</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=17 x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:num>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:num>21</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:num>37</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #666699 1pt solid" class=xl27 x:num>48</TD></TR></TBODY></TABLE>

But, if we make a helper column as the following one, where the number picked in cell A2 dissapears automatically as shown in A8. Once the formula is applied in A9 across E9 then the numbers picked will be different from the ones in A2 across E2.



<TABLE style="WIDTH: 383pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=511 x:str><COLGROUP><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 60pt" width=80><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" span=2 width=109><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" span=2 width=87><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; WIDTH: 29pt; HEIGHT: 12.75pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 width=39>*


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 60pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=80>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 82pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=109>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 82pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=109>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 65pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=87>D</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 65pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: #666699 1pt solid" class=xl27 width=87>E</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 height=17 x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>4, 5, 8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>12, 14, 18, 19</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>22, 24, 27, 29</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>33, 34, 36</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #666699 1pt solid" class=xl29>44, 45, 48</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 height=17 x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>27</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>36</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #666699 1pt solid" class=xl29 x:num>45</TD></TR></TBODY></TABLE>

Do you think this could work? Any suggestions will be helpfull.

Thanks and regards,
Moti.
 
Upvote 0
I've looked at this some more. If you don't mind a bit of a monster formula (maybe there's a better one?), you can do it without the helper row:

Excel Workbook
ABCDE
13, 4, 5, 811, 12, 14, 18, 1921, 22, 24, 27, 2933, 34, 36, 3742, 44, 45, 48
2519223444
3814213348
Random Selection



If you wanted the helper row anyway:

Excel Workbook
ABCDE
13, 4, 5, 811, 12, 14, 18, 1921, 22, 24, 27, 2933, 34, 36, 3742, 44, 45, 48
2312293648
3
7
84, 5, 811, 14, 18, 1921, 22, 24, 2733, 34, 3742, 44, 45
9814223744
Random Selection 2
 
Upvote 0
Hi Peter Sir,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I am really surprised and shocked to see the formula you’ve provided it’s like a miracle.<o:p></o:p>
I suggested the helper row because I didn’t know it could work with a single formula. The formula used without the helper row is extraordinary.<o:p></o:p>
<o:p></o:p>
I did search on the board too but couldn’t find any formula to my question, but now if you suggest this to another person he’ll definitely look towards your solution, which will be named ‘Peter solution’!!<o:p></o:p>
<o:p></o:p>
Thank you very much for your grateful help.<o:p></o:p>
<o:p></o:p>
Thanks and regards,<o:p></o:p>
Moti.<o:p></o:p>
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
825
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
592
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
R
  • Question
Random
Replies
1
Views
445
Fluff
Fluff

Forum statistics

Threads
1,214,907
Messages
6,122,185
Members
449,071
Latest member
cdnMech

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top