VBA or Formula to populate range of cells with set frequency of pre-defined values.

bartmaster

New Member
Joined
Jan 30, 2019
Messages
21
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,

Please see below table:
Book2
ABCDEFG
1ValuesValueOccurrenceNew count of values:65
2BBBAAA10%
3AAAAAB5%Values:
4AABABA15%
5ABAABB15%
6AAABAA15%
7ABBBAB15%
8BABBBA15%
9BBABBB10%
10BBB
11ABA
12BAB
13BAA
14BBA
15ABB
16BAA
17BBA
18ABA
19ABB
20BAB
21BAA
Sheet1
Cell Formulas
RangeFormula
D2:D9D2=COUNTIF($A$2:$A$21,C2)/20


Column C contains a list of distinct values from column A with their percentage distribution in column D.
Easy enough!

The question is how can we revert the process to get a list of pre-defined valuesfrom column C into column G (starting from row 4).
I would want to have them in random order but keeping the set distribution percentage?
Additionally a variable in the cell G1 would tell how many rows we would like to fill, i.e. currently column A has 20 values but in column F we would like to get a list of 65 values.

Any ideas? :)
Many thanks
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
@Peter_SSs
Many thanks for pointing this out.
I've updated my profile info and saved.
I would appreciate some help on this thread, if anything is not clear or any more information is required please let me know.
 
Upvote 0
I've updated my profile info and saved.
Thanks for that. (y)

Could you use something like this?
Note that it is not an exact science (see particularly the green cells) since when splitting the new count into the given percentages, some rounding will most likely need to be done since you cannot have two and a half rows of AAA. :)

You would have to use your 365 version for this method.

bartmaster_1.xlsm
ABCDEFG
1ValuesValueOccurrenceNew OccurrenceNew count of values:34
2BBBAAA10%4
3AAAAAB5%2Values:
4AABABA15%5BAB
5ABAABB15%5BBA
6AAABAA15%5AAA
7ABBBAB15%5AAB
8BABBBA15%5ABA
9BBABBB10%3ABA
10BBB BBB
11ABA ABA
12BAB BAB
13BAA ABB
14BBA AAA
15ABB ABB
16BAA BAB
17BBA BBA
18ABA BBB
19ABB ABB
20BAB BAA
21BAA ABA
22ABB
23BAA
24BBA
25BAA
26AAB
27AAA
28BBA
29AAA
30ABA
31BAB
32BAA
33BAB
34BBB
35BAA
36ABB
37BBA
38
Set % List
Cell Formulas
RangeFormula
G4:G37G4=SORTBY(FILTERXML(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("</c><c>",,REPT("</c><c>"&C2:C9,E2:E9)),"</c><c></c><c>","</c><c>"),"</c>","<p>",1)&"</c></p>","//c"),RANDARRAY(G1))
D2:D9D2=COUNTIF($A$2:$A$21,C2)/20
E2E2=G1-SUM(E3:E21)
E3:E21E3=IF(D3="","",ROUND(G$1*D3,0))
Dynamic array formulas.
 
Upvote 0
@Peter_SSs
Thank you very much for this. It works as expected. (y)?

Before I actually mark your reply as solution I am wondering if there are any VBA alternatives so this would also work on an earlier Excel version (not 365)?

Many thanks again!
Much appreciated ;)
 
Upvote 0
I am wondering if there are any VBA alternatives
Try this with a copy of your workbook.

VBA Code:
Sub New_List()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, Tsum As Long, NewCount As Long
  
  Randomize
  a = Range("C2", Range("D" & Rows.Count).End(xlUp)).Value
  NewCount = Range("G1").Value
  For i = 2 To UBound(a)
    a(i, 2) = Round(a(i, 2) * NewCount, 0)
    Tsum = Tsum + a(i, 2)
  Next i
  a(1, 2) = NewCount - Tsum
  ReDim b(1 To NewCount, 1 To 2)
  For i = 1 To UBound(a)
    For j = 1 To a(i, 2)
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = Rnd
    Next j
  Next i
  Application.ScreenUpdating = False
  With Range("G4:H4").Resize(NewCount)
    .Value = b
    .Sort Key1:=.Columns(2), Header:=xlNo
    .Columns(2).ClearContents
  End With
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

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