Hey all,
I am trying to transfer data with this formula
=IF(ROWS('Destination Sheet'!$B$5:B5)<=$A$1,INDEX('Reference Sheet'!$B$2:$B$23,AGGREGATE(15,3,('Reference Sheet'!$A$2:$A$23='Destination Sheet'!$I$1)/('Reference Sheet'!$A$2:$A$23='Destination Sheet'!$I$1)*(ROW('Reference Sheet'!$A$2:$A$23)-ROW('Reference Sheet'!$A$1)),ROWS('Destination Sheet'!$B$5:B5))),"")
and duplicate data with this one
=IFERROR(INDEX('Reference Sheet'!$B$2:$B$23,MATCH(1,SIGN(COUNTIF('Duplicated Sheet'!$B$1:B1,'Reference Sheet'!$B$2:$B$23)<SUMIF('Reference Sheet'!$B$2:$B$23,'Reference Sheet'!$B$2:$B$23,'Reference Sheet'!$F$2:$F$23)),0)),"")
the problem I am having is that the duplicate formula is making the numbers on this sheet, place into numerical order but I need them to stay in the order they were in on the reference sheet. (both pictured below)
The rows are duplicated based off of the # of laps completed.
I am wanting the data to be on this sheet in the end, based off of the number in the red cell which is the deciding cell. So I was thinking I could duplicate the cells on the reference sheet to the duplicated sheet with the numbers somehow staying in the order they are in on the reference sheet and then transfer them to the destination sheet.
If anyone has any ideas to get the same results that would be much appreciated.
I made a cheat sheet for both of the formulas used in simple wording terms and it helped me quite a bit.
Note: *f4* is not to be inserted, its to be pressed in order to make the selection absolute. (im sure you knew that but had to mention it)
For the first formula (to transfer rows):
In the first cell of desired column
=index(answer column *f4*, row # the answer first appears)
In a new cell
=Aggregate(15,3,(reference column *f4* = deciding cell *f4*)/(answer column *f4* = deciding cell *f4*)*(row(reference column*f4*)-row(header of reference column *f4*)), rows(top cell of desired column *f4*: top cell of desired column))
Copy and paste(replace) the aggregate formula where "row # the answer first appears" in the Index formula
In a new helper cell
=countif(reference column *f4*, deciding cell)
in the first cell of the desired column, before index
-if(copy/paste)<=helper cell *f4*, rest of forumla))),"")
and for the second formula(to duplicate rows):
In the first cell of desired column
=iferror(index(rows to repeat*f4*, match(1, sign(countif(header of desired column*f4* : header of desired column, rows to repeat*f4*)<sumif(rows to repeat*f4*, rows to repeat*f4*, column with x to repeat*f4*)),0)))
hopefully this helps things make a little bit more sense, because trust me I barely have any idea of what's going on either
Thank you in advance
I am trying to transfer data with this formula
=IF(ROWS('Destination Sheet'!$B$5:B5)<=$A$1,INDEX('Reference Sheet'!$B$2:$B$23,AGGREGATE(15,3,('Reference Sheet'!$A$2:$A$23='Destination Sheet'!$I$1)/('Reference Sheet'!$A$2:$A$23='Destination Sheet'!$I$1)*(ROW('Reference Sheet'!$A$2:$A$23)-ROW('Reference Sheet'!$A$1)),ROWS('Destination Sheet'!$B$5:B5))),"")
and duplicate data with this one
=IFERROR(INDEX('Reference Sheet'!$B$2:$B$23,MATCH(1,SIGN(COUNTIF('Duplicated Sheet'!$B$1:B1,'Reference Sheet'!$B$2:$B$23)<SUMIF('Reference Sheet'!$B$2:$B$23,'Reference Sheet'!$B$2:$B$23,'Reference Sheet'!$F$2:$F$23)),0)),"")
the problem I am having is that the duplicate formula is making the numbers on this sheet, place into numerical order but I need them to stay in the order they were in on the reference sheet. (both pictured below)
The rows are duplicated based off of the # of laps completed.
| Excel Doc for Row Transfers and Repetitions.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Group # | Racer 1 | Racer 2 | Pair # | Total Length Ran (ft) | Laps | ||
| 2 | 1 | Mark | Trevor | #1 | 2500 | 1 | ||
| 3 | 1 | Adam | Matt | #2 | 2500 | 1 | ||
| 4 | 1 | Carl | Maddy | #3 | 3000 | 1 | ||
| 5 | 1 | Bryan | Steve | #4 | 3000 | 1 | ||
| 6 | 1 | Claudia | Gladdis | #5 | 3000 | 1 | ||
| 7 | 1 | Olivia | Terrance | #6 | 2000 | 1 | ||
| 8 | 2 | Boris | Helen | #7 | 2000 | 1 | ||
| 9 | 2 | Sam | Robert | #8 | 2000 | 1 | ||
| 10 | 2 | Jordan | William | #9 | 2000 | 1 | ||
| 11 | 2 | Blake | Brody | #10 | 2000 | 1 | ||
| 12 | 2 | Izzy | Erin | #11 | 2000 | 1 | ||
| 13 | 2 | Izzy | Erin | #11 | 2000 | 1 | ||
| 14 | 3 | Bryn | Catherine | #12 | 2000 | 1 | ||
| 15 | 3 | Bryn | Catherine | #12 | 2000 | 1 | ||
| 16 | 3 | Cali | Maya | #13 | 2000 | 1 | ||
| 17 | 3 | Mia | Bri | #14 | 1500 | 1 | ||
| 18 | 3 | Alice | Lauren | #15 | 1500 | 1 | ||
| 19 | 3 | Bill | Kate | #16 | 1500 | 1 | ||
| 20 | 4 | Lena | Elizabeth | #17 | 1500 | 1 | ||
| 21 | 4 | Beth | Courtney | #18 | 1500 | 1 | ||
| 22 | 4 | Hailey | Rena | #19 | 1500 | 2 | ||
| 23 | 4 | Lana | Bert | #20 | 1000 | 2 | ||
| 24 | 4 | Chris | Bob | #21 | 1000 | 2 | ||
| 25 | 4 | Larry | Bernie | #22 | 1000 | 2 | ||
Duplicated Sheet | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A2:A25 | A2 | =IFERROR(INDEX('Reference Sheet'!$A$2:$A$23,MATCH(1,SIGN(COUNTIF('Duplicated Sheet'!$A$1:A1,'Reference Sheet'!$A$2:$A$23)<SUMIF('Reference Sheet'!$A$2:$A$23,'Reference Sheet'!$A$2:$A$23,'Reference Sheet'!$F$2:$F$23)),0)),"") |
| B2 | B2 | =IFERROR(INDEX('Reference Sheet'!$B$2:$B$23,MATCH(1,SIGN(COUNTIF('Duplicated Sheet'!$B$1:B1,'Reference Sheet'!$B$2:$B$23)<SUMIF('Reference Sheet'!$B$2:$B$23,'Reference Sheet'!$B$2:$B$23,'Reference Sheet'!$F$2:$F$23)),0)),"") |
| C2:C25 | C2 | =IFERROR(INDEX('Reference Sheet'!$C$2:$C$23,MATCH(1,SIGN(COUNTIF('Duplicated Sheet'!$C$1:C1,'Reference Sheet'!$C$2:$C$23)<SUMIF('Reference Sheet'!$C$2:$C$23,'Reference Sheet'!$C$2:$C$23,'Reference Sheet'!$F$2:$F$23)),0)),"") |
| D2:D25 | D2 | =IFERROR(INDEX('Reference Sheet'!$D$2:$D$23,MATCH(1,SIGN(COUNTIF('Duplicated Sheet'!$D$1:D1,'Reference Sheet'!$D$2:$D$23)<SUMIF('Reference Sheet'!$D$2:$D$23,'Reference Sheet'!$D$2:$D$23,'Reference Sheet'!$F$2:$F$23)),0)),"") |
| E2:E25 | E2 | =IFERROR(INDEX('Reference Sheet'!$E$2:$E$23,MATCH(1,SIGN(COUNTIF('Duplicated Sheet'!$E$1:E1,'Reference Sheet'!$E$2:$E$23)<SUMIF('Reference Sheet'!$E$2:$E$23,'Reference Sheet'!$E$2:$E$23,'Reference Sheet'!$F$2:$F$23)),0)),"") |
| F2:F25 | F2 | =IFERROR(INDEX('Reference Sheet'!$F$2:$F$23,MATCH(1,SIGN(COUNTIF('Duplicated Sheet'!$F$1:F1,'Reference Sheet'!$F$2:$F$23)<SUMIF('Reference Sheet'!$F$2:$F$23,'Reference Sheet'!$F$2:$F$23,'Reference Sheet'!$F$2:$F$23)),0)),"") |
| B3:B25 | B3 | =IFERROR(INDEX('Reference Sheet'!$B$2:$B$23,MATCH(1,SIGN(COUNTIF('Duplicated Sheet'!$B$1:B2,'Reference Sheet'!$B$2:$B$23)<SUMIF('Reference Sheet'!$B$2:$B$23,'Reference Sheet'!$B$2:$B$23,'Reference Sheet'!$F$2:$F$23)),0)),"") |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
| Excel Doc for Row Transfers and Repetitions.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Group # | Racer 1 | Racer 2 | Pair # | Total Length Ran (ft) | Laps | ||
| 2 | 1 | Mark | Trevor | #1 | 2500 | 1 | ||
| 3 | 2 | Adam | Matt | #2 | 3000 | 1 | ||
| 4 | 3 | Carl | Maddy | #3 | 2000 | 1 | ||
| 5 | 4 | Bryan | Steve | #4 | 2000 | 1 | ||
| 6 | 1 | Claudia | Gladdis | #5 | 1500 | 1 | ||
| 7 | 2 | Olivia | Terrance | #6 | 2000 | 1 | ||
| 8 | 3 | Boris | Helen | #7 | 1000 | 1 | ||
| 9 | 4 | Sam | Robert | #8 | 3000 | 1 | ||
| 10 | 1 | Jordan | William | #9 | 2500 | 1 | ||
| 11 | 2 | Blake | Brody | #10 | 3000 | 1 | ||
| 12 | 3 | Izzy | Erin | #11 | 1500 | 2 | ||
| 13 | 4 | Bryn | Catherine | #12 | 2000 | 2 | ||
| 14 | 1 | Cali | Maya | #13 | 2000 | 1 | ||
| 15 | 2 | Mia | Bri | #14 | 2000 | 1 | ||
| 16 | 3 | Alice | Lauren | #15 | 2000 | 1 | ||
| 17 | 4 | Bill | Kate | #16 | 2000 | 1 | ||
| 18 | 1 | Lena | Elizabeth | #17 | 1500 | 1 | ||
| 19 | 2 | Beth | Courtney | #18 | 1000 | 1 | ||
| 20 | 3 | Hailey | Rena | #19 | 2000 | 1 | ||
| 21 | 4 | Lana | Bert | #20 | 1500 | 1 | ||
| 22 | 1 | Chris | Bob | #21 | 1500 | 1 | ||
| 23 | 2 | Larry | Bernie | #22 | 1000 | 1 | ||
Reference Sheet | ||||||||
I am wanting the data to be on this sheet in the end, based off of the number in the red cell which is the deciding cell. So I was thinking I could duplicate the cells on the reference sheet to the duplicated sheet with the numbers somehow staying in the order they are in on the reference sheet and then transfer them to the destination sheet.
| Excel Doc for Row Transfers and Repetitions.xlsx | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | 6 | Group # | 1 | |||||||||||||
| 2 | Total Reel Length in meters: | 3505 | ||||||||||||||
| 3 | Laps | |||||||||||||||
| 4 | Racer 1 | Racer 2 | Pair # | TOTAL LENGTH RAN (ft) | TOTAL LENGTH RAN (m) | |||||||||||
| 5 | Mark | Trevor | #1 | 2500 | 762 | |||||||||||
| 6 | Claudia | Gladdis | #5 | 1500 | 457 | |||||||||||
| 7 | Jordan | William | #9 | 2500 | 762 | |||||||||||
| 8 | Cali | Maya | #13 | 2000 | 610 | |||||||||||
| 9 | Lena | Elizabeth | #17 | 1500 | 457 | |||||||||||
| 10 | Chris | Bob | #21 | 1500 | 457 | |||||||||||
| 11 | 0 | 0 | ||||||||||||||
| 12 | 0 | 0 | ||||||||||||||
| 13 | 0 | 0 | ||||||||||||||
| 14 | 0 | 0 | ||||||||||||||
Destination Sheet | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A1 | A1 | =COUNTIF('Reference Sheet'!$A$2:$A$23,'Destination Sheet'!I1) |
| I2 | I2 | =SUM(M5:N50) |
| B5:B14 | B5 | =IF(ROWS('Destination Sheet'!$B$5:B5)<=$A$1,INDEX('Reference Sheet'!$B$2:$B$23,AGGREGATE(15,3,('Reference Sheet'!$A$2:$A$23='Destination Sheet'!$I$1)/('Reference Sheet'!$A$2:$A$23='Destination Sheet'!$I$1)*(ROW('Reference Sheet'!$A$2:$A$23)-ROW('Reference Sheet'!$A$1)),ROWS('Destination Sheet'!$B$5:B5))),"") |
| C5:C14 | C5 | =IF(ROWS('Destination Sheet'!$C$5:C5)<=$A$1,INDEX('Reference Sheet'!$C$2:$C$23,AGGREGATE(15,3,('Reference Sheet'!$A$2:$A$23='Destination Sheet'!$I$1)/('Reference Sheet'!$A$2:$A$23='Destination Sheet'!$I$1)*(ROW('Reference Sheet'!$A$2:$A$23)-ROW('Reference Sheet'!$A$1)),ROWS('Destination Sheet'!$C$5:C5))),"") |
| G5:G14 | G5 | =IF(ROWS('Destination Sheet'!$G$5:G5)<=$A$1,INDEX('Reference Sheet'!$D$2:$D$23,AGGREGATE(15,3,('Reference Sheet'!$A$2:$A$23='Destination Sheet'!$I$1)/('Reference Sheet'!$A$2:$A$23='Destination Sheet'!$I$1)*(ROW('Reference Sheet'!$A$2:$A$23)-ROW('Reference Sheet'!$A$1)),ROWS('Destination Sheet'!$G$5:G5))),"") |
| J5:J14 | J5 | =IF(ROWS('Destination Sheet'!$J$5:J5)<=$A$1,INDEX('Reference Sheet'!$E$2:$E$23,AGGREGATE(15,3,('Reference Sheet'!$A$2:$A$23='Destination Sheet'!$I$1)/('Reference Sheet'!$A$2:$A$23='Destination Sheet'!$I$1)*(ROW('Reference Sheet'!$A$2:$A$23)-ROW('Reference Sheet'!$A$1)),ROWS('Destination Sheet'!$J$5:J5))),"0") |
| M5:M14 | M5 | =CONVERT(J5,"ft","m") |
If anyone has any ideas to get the same results that would be much appreciated.
I made a cheat sheet for both of the formulas used in simple wording terms and it helped me quite a bit.
Note: *f4* is not to be inserted, its to be pressed in order to make the selection absolute. (im sure you knew that but had to mention it)
For the first formula (to transfer rows):
In the first cell of desired column
=index(answer column *f4*, row # the answer first appears)
In a new cell
=Aggregate(15,3,(reference column *f4* = deciding cell *f4*)/(answer column *f4* = deciding cell *f4*)*(row(reference column*f4*)-row(header of reference column *f4*)), rows(top cell of desired column *f4*: top cell of desired column))
Copy and paste(replace) the aggregate formula where "row # the answer first appears" in the Index formula
In a new helper cell
=countif(reference column *f4*, deciding cell)
in the first cell of the desired column, before index
-if(copy/paste)<=helper cell *f4*, rest of forumla))),"")
and for the second formula(to duplicate rows):
In the first cell of desired column
=iferror(index(rows to repeat*f4*, match(1, sign(countif(header of desired column*f4* : header of desired column, rows to repeat*f4*)<sumif(rows to repeat*f4*, rows to repeat*f4*, column with x to repeat*f4*)),0)))
hopefully this helps things make a little bit more sense, because trust me I barely have any idea of what's going on either
Thank you in advance
