Forum Discussion

Janine_Dismukes's avatar
Janine_Dismukes
Copper Contributor
May 30, 2023

Reverse engineer data sheets from excel spreadsheet with summary percentages.

I need to make 100 individual data sheets with the answers that correspond to this summary sheet:       Yes Refused Not offered Red Yellow Blue   Code Question               ...
excel
Janine_Dismukes's avatar
Janine_Dismukes
Copper Contributor
May 31, 2023

mtarler 

Here is the summary page:

  YesRefusedNot offeredRedYellowBlue
CodeQuestion      
        
G1aDid the dog eat his food?98%1%1%   
G2aWhat color is the ball?   95%1%4%
G2bWhat color is the dress?   28%34%38%

 

I need to make 100 sheets of this same page, but have the computer enter the answers, like this:

5/30/2023 YesRefusedNot offeredRedYellowBlue
CodeQuestion      
        
G1aDid the dog eat his food?x     
G2aWhat color is the ball?   x  
G2bWhat color is the dress?    x 
mtarler's avatar
mtarler
Silver Contributor
May 31, 2023
oh and you need those answers to be in the percentages shown in the original (hence the reverse engineering part) and let me guess the combinations need to be random too? So we can't give a solution with sheets#1-N be the first answer where N is the percentage for that first response and then sheets#N+1 to M be the next response% where M-N is the second response %, ... But instead you want random selections / combinations for the positions of each corresponding response?
  • mtarler's avatar
    mtarler
    Silver Contributor
    May 31, 2023

    Janine_Dismukes  OK I think this may work for you.  In the attached I have this formula:

    =LET(in,Sheet1!$C2:$I2, totalnum, TotalN, nthVal, $L$1,
  cumulative,SCAN(0,totalnum*in,LAMBDA(p,q,p+q)),
  rseq,SORTBY(SEQUENCE(totalnum),PRNG(SEQUENCE(totalnum,,seed+10*ROW(),seed))),
  nth, XMATCH(nthVal,rseq),
  col, XMATCH(nth,INT(cumulative),1,1),
  rowout, TEXTSPLIT(MID(REPT(".",col)&col&REPT(".",COLUMNS(in)-col+1),2,COLUMNS(in)),".",,FALSE),
  rowout)

    It uses a custom pseudoRandom number generator I created to create a random sequence based on a seed.  You can change the seed on Sheet1 in the attached ("seed" is named cell Sheet1!$L$1 in this case) and that "seed" is then offset by 10*ROW() so each row will have a different random sequence.  Each sheet will have its own 'index' into that random sequence based on cell $L$1 which has the formula =Sheet()-1 in sheets 3&4 of this example but in sheet 2 I demonstrate how you can type any number in the range to see that result (i.e. you only need Sheet2 and you can change the value in $L$1 to see/simulate any of the 100 outputs (or whatever total value you enter in Sheet1!$L$2)

    I have not tested every edge condition and combination but it seems to be working pretty well.

Resources