I have 2 separate formulas

=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

=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)),"")

They work almost just the way I want separately.

Issue is I need them to work as one, to result in the same cell. I have attached my reference document.

I was thinking I could duplicate the rows to a separate sheet (second formula) and then just move the data based on the value of my reference cell (red) with the first formula to where I need it to be in another sheet, but what seems to be happening with my numbers where I am trying to duplicate, is they are going into numerical order instead of alternating like they do on my first sheet, which I need them to do. I need the rows to stay in the same order, just duplicated so I can extract the data into the sheet where I need them to be.

What I was trying to do at first (before my glorious failure of an idea) was merge both formulas to get the rows to transfer and duplicate into the same column based on one cells value.

If anyone has a better idea to get the same results, PLEASE SHARE.

Thank you in advance!

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 | 5 | ||

3 | 2 | Adam | Matt | #2 | 3000 | 6 | ||

4 | 3 | Carl | Maddy | #3 | 2000 | 4 | ||

5 | 4 | Bryan | Steve | #4 | 2000 | 4 | ||

6 | 1 | Claudia | Gladdis | #5 | 1500 | 3 | ||

7 | 2 | Olivia | Terrance | #6 | 2000 | 4 | ||

8 | 3 | Boris | Helen | #7 | 1000 | 2 | ||

9 | 4 | Sam | Robert | #8 | 3000 | 6 | ||

10 | 1 | Jordan | William | #9 | 2500 | 5 | ||

11 | 2 | Blake | Brody | #10 | 3000 | 6 | ||

12 | 3 | Izzy | Erin | #11 | 1500 | 3 | ||

13 | 4 | Bryn | Catherine | #12 | 2000 | 4 | ||

14 | 1 | Cali | Maya | #13 | 2000 | 4 | ||

15 | 2 | Mia | Bri | #14 | 2000 | 4 | ||

16 | 3 | Alice | Lauren | #15 | 2000 | 4 | ||

17 | 4 | Bill | Kate | #16 | 2000 | 4 | ||

18 | 1 | Lena | Elizabeth | #17 | 1500 | 3 | ||

19 | 2 | Beth | Courtney | #18 | 1000 | 2 | ||

20 | 3 | Hailey | Rena | #19 | 2000 | 4 | ||

21 | 4 | Lana | Bert | #20 | 1500 | 3 | ||

22 | 1 | Chris | Bob | #21 | 1500 | 3 | ||

23 | 2 | Larry | Bernie | #22 | 1000 | 2 | ||

Reference Sheet |

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 | 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 | ||||||||||||||

15 | 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:B15 | 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:C15 | 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:G15 | 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:J15 | 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:M15 | M5 | =CONVERT(J5,"ft","m") |