Generate 'sample' data from data tables without duplicating some values and by having a clear order and relation between some of the sample values


New Member
Apr 24, 2014
So, I have a pretty specific thing to do that is rather hard to explain in the title but I hope it gives a general idea. Now, on to what I actually would like to be able to do and I'm not sure what is the best way to tackle it:

* have a set of data tables that are something like:

A - 100 different values
B - 6 different values
C - C1 - 4 different values
- C2 - 5 different values
D - 6 different values

* Based on these 'data sets' I need to generate 20 samples randomly but also keep in mind if one value was used so to not use it again until there are no more 'unique values' to use. As an additional thing, C1 and C2 values have a special relation and order of
C1 - C2: 1-1, 1-2, 1-3, 1-4, 1-5, 2-1, 2-2, 2-3 etc. 4-4, 4-5 in this order

* There would be 20 tables generated from the whole data set that is based on the order and relation between C1 and C2 (4*5 = 20 samples).

* So, since there will be only 20 values used from table A, next time I generate new 20 samples it would have to use the 'remaining' values only (100-20=80 unused values).

A set of 20 samples would be something like:

Sample 1 - A[23], B[3], C1[1], C2[1], D[2]
Sample 2 - A[76], B[5], C1[1], C2[2], D[1]
Sample 3 - A[15], B[6], C1[1], C2[3], D[6]
Sample 4 - A[6], B[2], C1[1], C2[4], D[5]
Sample 5 - A[64], B[1], C1[1], C2[5], D[4]

Sample 6 - A[55], B[4], C1[2], C2[1], D[3]
Sample 7 - A[29], B[4], C1[2], C2[2], D[2]
Sample 8 - A[1], B[2], C1[2], C2[3], D[6]
Sample 9 - A[87], B[3], C1[2], C2[4], D[5]
Sample 10 - A[2], B[6], C1[2], C2[5], D[4]

Sample 11 - A[10], B[1], C1[3], C2[1], D[3]
Sample 12 - A[33], B[5], C1[3], C2[2], D[1]
Sample 13 - A[65], B[4], C1[3], C2[3], D[2]
Sample 14 - A[64], B[3], C1[3], C2[4], D[3]
Sample 15 - A[63], B[1], C1[3], C2[5], D[5]

Sample 16 - A[98], B[2], C1[4], C2[1], D[1]
Sample 17 - A[66], B[6], C1[4], C2[2], D[6]
Sample 18 - A[43], B[5], C1[4], C2[3], D[4]
Sample 19 - A[44], B[6], C1[4], C2[4], D[2]
Sample 20 - A[92], B[4], C1[4], C2[5], D[2]

At the next batch of 20 samples, the A values should never repeat (so, values used in the first 20 samples are 'removed' from the data set), B values don't repeat until all 6 of them are used in consecutive samples, C1 and C2 values respect a clear and fixed order, and D has the same property as B.

The colors are there to show that there is no duplicate value of B and D between 6 consecutive samples.

In the end, with 5 sets of 20 such samples, the whole 100 A values should be covered.

Hope this explains well enough what I need but if there are any other questions don't hesitate to ask. I am thinking of using Pivot tables but I must admit I haven't used them at all and I am not sure what their limitations are to be able to clearly know I should be able to do it in the end.

LE: forgot to add one important detail, the values are not limited to 100/6/6 unique values, these are the initial parameters that might change in time. The idea behind this is to be able to generate randomly 20 samples based on the relation between C1 and C2, not have duplicate values until all A, B and D values are used on previous samples.
Last edited:


New Member
Apr 24, 2014
There is no way to generate tables based on input data and some relationships?

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...