Hello, I have what I imagine is a pretty big project. I’m not well-versed in code so I’m hoping you can help. It would be much appreciated and I thank you in advance for any help you can provide!
What I’m trying to do is come up with 5,859,375 rows of data (in 6 sheets). I know it’s a lot, more on that below, and this is how I’d like to go about it.
The cells in Columns B-O on “Sheet1” say either “yes” or “no”
I’d like the formula/code to do the following…
Group 1 (results in row 2 of “Sheet2”):
I’d like to find the first “yes” (from the top) of each column (B-O) from “Sheet 1.” (see Different Combinations below)
Then copy cell P from the Column B “yes” row to B2 of “Sheet2”
Then copy cell Q from the Column B “yes” row to C2 of “Sheet2”
Then copy cell P from the Column C “yes” row to D2 of “Sheet2”
Then copy cell Q from the Column C “yes” row to E2 of “Sheet2”
Then copy cell P from the Column D “yes” row to F2 of “Sheet2”
Then copy cell Q from the Column D “yes” row to G2 of “Sheet2”
Then copy cell P from the Column E “yes” row to H2 of “Sheet2”
Then copy cell Q from the Column E “yes” row to I2 of “Sheet2”
Then copy cell P from the Column F “yes” row to J2 of “Sheet2”
Then copy cell Q from the Column F “yes” row to K2 of “Sheet2”
Then copy cell P from the Column G “yes” row to L2 of “Sheet2”
Then copy cell Q from the Column G “yes” row to M2 of “Sheet2”
Then copy cell P from the Column H “yes” row to N2 of “Sheet2”
Then copy cell Q from the Column H “yes” row to O2 of “Sheet2”
Then copy cell P from the Column I “yes” row to P2 of “Sheet2”
Then copy cell Q from the Column I “yes” row to Q2 of “Sheet2”
Then copy cell P from the Column J “yes” row to R2 of “Sheet2”
Then copy cell Q from the Column J “yes” row to S2 of “Sheet2”
Then copy cell P from the Column K “yes” row to T2 of “Sheet2”
Then copy cell Q from the Column K “yes” row to U2 of “Sheet2”
Then copy cell P from the Column L “yes” row to V2 of “Sheet2”
Then copy cell Q from the Column L “yes” row to W2 of “Sheet2”
Then copy cell P from the Column M “yes” row to X2 of “Sheet2”
Then copy cell Q from the Column M “yes” row to Y2 of “Sheet2”
Then copy cell P from the Column N “yes” row to Z2 of “Sheet2”
Then copy cell Q from the Column N “yes” row to AA2 of “Sheet2”
Then copy cell P from the Column O “yes” row to AB2 of “Sheet2”
Then copy cell Q from the Column O “yes” row to AC2 of “Sheet2”
Sum of U for all 14 “yes” rows in group divided by sum of T for all 14 “yes” rows in group, result entered in AD2 of “Sheet2”
Sum of V for all 14 in group, result entered in AE2 of “Sheet2”
Sum of W for all 14 in group, result entered in AF2 of “Sheet2”
Sum of X for all 14 in group, result entered in AG2 of “Sheet2”
Sum of Y for all 14 in group, result entered in AH2 of “Sheet2”
Group 2 (results in row 3 of “Sheet2”):
I’d like to find the first “yes” (from the top) of columns C-O, and second “yes” from column B from “Sheet 1.” (see Different Combinations below)
Then copy cell P from the Column B “yes” row to B3 of “Sheet2”
Then copy cell Q from the Column B “yes” row to C3 of “Sheet2”
Then copy cell P from the Column C “yes” row to D3 of “Sheet2”
Then copy cell Q from the Column C “yes” row to E3 of “Sheet2”
Then copy cell P from the Column D “yes” row to F3 of “Sheet2”
Then copy cell Q from the Column D “yes” row to G3 of “Sheet2”
Then copy cell P from the Column E “yes” row to H3 of “Sheet2”
Then copy cell Q from the Column E “yes” row to I3 of “Sheet2”
Then copy cell P from the Column F “yes” row to J3 of “Sheet2”
Then copy cell Q from the Column F “yes” row to K3 of “Sheet2”
Then copy cell P from the Column G “yes” row to L3 of “Sheet2”
Then copy cell Q from the Column G “yes” row to M3 of “Sheet2”
Then copy cell P from the Column H “yes” row to N3 of “Sheet2”
Then copy cell Q from the Column H “yes” row to O3 of “Sheet2”
Then copy cell P from the Column I “yes” row to P3 of “Sheet2”
Then copy cell Q from the Column I “yes” row to Q3 of “Sheet2”
Then copy cell P from the Column J “yes” row to R3 of “Sheet2”
Then copy cell Q from the Column J “yes” row to S3 of “Sheet2”
Then copy cell P from the Column K “yes” row to T3 of “Sheet2”
Then copy cell Q from the Column K “yes” row to U3 of “Sheet2”
Then copy cell P from the Column L “yes” row to V3 of “Sheet2”
Then copy cell Q from the Column L “yes” row to W3 of “Sheet2”
Then copy cell P from the Column M “yes” row to X3 of “Sheet2”
Then copy cell Q from the Column M “yes” row to Y3 of “Sheet2”
Then copy cell P from the Column N “yes” row to Z3 of “Sheet2”
Then copy cell Q from the Column N “yes” row to AA3 of “Sheet2”
Then copy cell P from the Column O “yes” row to AB3 of “Sheet2”
Then copy cell Q from the Column O “yes” row to AC3 of “Sheet2”
Sum of U for all 14 “yes” rows in group divided by sum of T for all 14 “yes” rows in group, result entered in AD3 of “Sheet2”
Sum of V for all 14 in group, result entered in AE3 of “Sheet2”
Sum of W for all 14 in group, result entered in AF3 of “Sheet2”
Sum of X for all 14 in group, result entered in AG3 of “Sheet2”
Sum of Y for all 14 in group, result entered in AH3 of “Sheet2”
I’d like to do this all the way through to Group 5,859,375. Since an Excel spreadsheet only holds about 1,000,000 rows I’ll need the output on about 6 sheets. These can be called “Sheet2” “Sheet3”… “Sheet 6.”
Different Combinations:
There are 5,859,375 groups because if you multiply all of the “yes” variations below: 3 x 1 x 1 x 5^9 x 1 x1. See below…
Column B First “yes,” second “yes,” third “yes”
Column C First “yes”
Column D First “yes”
Column E First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column F First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column G First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column H First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column I First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column J First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column K First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column L First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column M First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column N First “yes”
Column O First “yes”
Let me know if you have any questions and if there is anything I can clarify. Thank you in advance for your help!
What I’m trying to do is come up with 5,859,375 rows of data (in 6 sheets). I know it’s a lot, more on that below, and this is how I’d like to go about it.
The cells in Columns B-O on “Sheet1” say either “yes” or “no”
I’d like the formula/code to do the following…
Group 1 (results in row 2 of “Sheet2”):
I’d like to find the first “yes” (from the top) of each column (B-O) from “Sheet 1.” (see Different Combinations below)
Then copy cell P from the Column B “yes” row to B2 of “Sheet2”
Then copy cell Q from the Column B “yes” row to C2 of “Sheet2”
Then copy cell P from the Column C “yes” row to D2 of “Sheet2”
Then copy cell Q from the Column C “yes” row to E2 of “Sheet2”
Then copy cell P from the Column D “yes” row to F2 of “Sheet2”
Then copy cell Q from the Column D “yes” row to G2 of “Sheet2”
Then copy cell P from the Column E “yes” row to H2 of “Sheet2”
Then copy cell Q from the Column E “yes” row to I2 of “Sheet2”
Then copy cell P from the Column F “yes” row to J2 of “Sheet2”
Then copy cell Q from the Column F “yes” row to K2 of “Sheet2”
Then copy cell P from the Column G “yes” row to L2 of “Sheet2”
Then copy cell Q from the Column G “yes” row to M2 of “Sheet2”
Then copy cell P from the Column H “yes” row to N2 of “Sheet2”
Then copy cell Q from the Column H “yes” row to O2 of “Sheet2”
Then copy cell P from the Column I “yes” row to P2 of “Sheet2”
Then copy cell Q from the Column I “yes” row to Q2 of “Sheet2”
Then copy cell P from the Column J “yes” row to R2 of “Sheet2”
Then copy cell Q from the Column J “yes” row to S2 of “Sheet2”
Then copy cell P from the Column K “yes” row to T2 of “Sheet2”
Then copy cell Q from the Column K “yes” row to U2 of “Sheet2”
Then copy cell P from the Column L “yes” row to V2 of “Sheet2”
Then copy cell Q from the Column L “yes” row to W2 of “Sheet2”
Then copy cell P from the Column M “yes” row to X2 of “Sheet2”
Then copy cell Q from the Column M “yes” row to Y2 of “Sheet2”
Then copy cell P from the Column N “yes” row to Z2 of “Sheet2”
Then copy cell Q from the Column N “yes” row to AA2 of “Sheet2”
Then copy cell P from the Column O “yes” row to AB2 of “Sheet2”
Then copy cell Q from the Column O “yes” row to AC2 of “Sheet2”
Sum of U for all 14 “yes” rows in group divided by sum of T for all 14 “yes” rows in group, result entered in AD2 of “Sheet2”
Sum of V for all 14 in group, result entered in AE2 of “Sheet2”
Sum of W for all 14 in group, result entered in AF2 of “Sheet2”
Sum of X for all 14 in group, result entered in AG2 of “Sheet2”
Sum of Y for all 14 in group, result entered in AH2 of “Sheet2”
Group 2 (results in row 3 of “Sheet2”):
I’d like to find the first “yes” (from the top) of columns C-O, and second “yes” from column B from “Sheet 1.” (see Different Combinations below)
Then copy cell P from the Column B “yes” row to B3 of “Sheet2”
Then copy cell Q from the Column B “yes” row to C3 of “Sheet2”
Then copy cell P from the Column C “yes” row to D3 of “Sheet2”
Then copy cell Q from the Column C “yes” row to E3 of “Sheet2”
Then copy cell P from the Column D “yes” row to F3 of “Sheet2”
Then copy cell Q from the Column D “yes” row to G3 of “Sheet2”
Then copy cell P from the Column E “yes” row to H3 of “Sheet2”
Then copy cell Q from the Column E “yes” row to I3 of “Sheet2”
Then copy cell P from the Column F “yes” row to J3 of “Sheet2”
Then copy cell Q from the Column F “yes” row to K3 of “Sheet2”
Then copy cell P from the Column G “yes” row to L3 of “Sheet2”
Then copy cell Q from the Column G “yes” row to M3 of “Sheet2”
Then copy cell P from the Column H “yes” row to N3 of “Sheet2”
Then copy cell Q from the Column H “yes” row to O3 of “Sheet2”
Then copy cell P from the Column I “yes” row to P3 of “Sheet2”
Then copy cell Q from the Column I “yes” row to Q3 of “Sheet2”
Then copy cell P from the Column J “yes” row to R3 of “Sheet2”
Then copy cell Q from the Column J “yes” row to S3 of “Sheet2”
Then copy cell P from the Column K “yes” row to T3 of “Sheet2”
Then copy cell Q from the Column K “yes” row to U3 of “Sheet2”
Then copy cell P from the Column L “yes” row to V3 of “Sheet2”
Then copy cell Q from the Column L “yes” row to W3 of “Sheet2”
Then copy cell P from the Column M “yes” row to X3 of “Sheet2”
Then copy cell Q from the Column M “yes” row to Y3 of “Sheet2”
Then copy cell P from the Column N “yes” row to Z3 of “Sheet2”
Then copy cell Q from the Column N “yes” row to AA3 of “Sheet2”
Then copy cell P from the Column O “yes” row to AB3 of “Sheet2”
Then copy cell Q from the Column O “yes” row to AC3 of “Sheet2”
Sum of U for all 14 “yes” rows in group divided by sum of T for all 14 “yes” rows in group, result entered in AD3 of “Sheet2”
Sum of V for all 14 in group, result entered in AE3 of “Sheet2”
Sum of W for all 14 in group, result entered in AF3 of “Sheet2”
Sum of X for all 14 in group, result entered in AG3 of “Sheet2”
Sum of Y for all 14 in group, result entered in AH3 of “Sheet2”
I’d like to do this all the way through to Group 5,859,375. Since an Excel spreadsheet only holds about 1,000,000 rows I’ll need the output on about 6 sheets. These can be called “Sheet2” “Sheet3”… “Sheet 6.”
Different Combinations:
There are 5,859,375 groups because if you multiply all of the “yes” variations below: 3 x 1 x 1 x 5^9 x 1 x1. See below…
Column B First “yes,” second “yes,” third “yes”
Column C First “yes”
Column D First “yes”
Column E First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column F First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column G First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column H First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column I First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column J First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column K First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column L First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column M First “yes,” second “yes,” third “yes,” fourth “yes,” fifth “yes”
Column N First “yes”
Column O First “yes”
Let me know if you have any questions and if there is anything I can clarify. Thank you in advance for your help!
