Forum Discussion
Reverse engineer data sheets from excel spreadsheet with summary percentages.
I was referred here by the Excel Customer Service because they did not know how to help me. I do not know what VBA coding is.
The question is why do you need 100 data sheets. and what do those data sheets need to look like?
I would recommend against creating 100 data sheets and there could be a better solution.
But in simple terms for what you ask, you could
a) create 100 data sheets
b) highlight / copy header
c) highlight/select all 100 data sheets
d) paste header where you want it
e) Use a formula like =index(sheet1!a2:m102, sheet()-1, )
Here is the summary page:
Yes Refused Not offered Red Yellow Blue Code Question G1a Did the dog eat his food? 98% 1% 1% G2a What color is the ball? 95% 1% 4% G2b What 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 Yes Refused Not offered Red Yellow Blue Code Question G1a Did the dog eat his food? x G2a What color is the ball? x G2b What color is the dress? x - 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?
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.