Combining two formulas that work perfectly on their own, but I need the result in the same cell/column

Kurtin

New Member
Joined
Jan 20, 2021
Messages
27
Office Version
  1. 365
Platform
  1. Windows
Hey, so I need some help.



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
ABCDEF
1Group #Racer 1Racer 2Pair #Total Length Ran (ft)Laps
21MarkTrevor#125005
32AdamMatt#230006
43CarlMaddy#320004
54BryanSteve#420004
61ClaudiaGladdis#515003
72OliviaTerrance#620004
83BorisHelen#710002
94SamRobert#830006
101JordanWilliam#925005
112BlakeBrody#1030006
123IzzyErin#1115003
134BrynCatherine#1220004
141CaliMaya#1320004
152MiaBri#1420004
163AliceLauren#1520004
174BillKate#1620004
181LenaElizabeth#1715003
192BethCourtney#1810002
203HaileyRena#1920004
214LanaBert#2015003
221ChrisBob#2115003
232LarryBernie#2210002
Reference Sheet


Cell Formulas
RangeFormula
A2:A25A2=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)),"")
B2B2=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:C25C2=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:D25D2=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:E25E2=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:F25F2=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:B25B3=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
ABCDEFGHIJKLMN
16Group #1
2Total Reel Length in meters:3505
3Laps
4Racer 1Racer 2Pair #TOTAL LENGTH RAN (ft)TOTAL LENGTH RAN (m)
5MarkTrevor#12500762
6ClaudiaGladdis#51500457
7JordanWilliam#92500762
8CaliMaya#132000610
9LenaElizabeth#171500457
10ChrisBob#211500457
11   00
12   00
13   00
14   00
15   00
Destination Sheet
Cell Formulas
RangeFormula
A1A1=COUNTIF('Reference Sheet'!$A$2:$A$23,'Destination Sheet'!I1)
I2I2=SUM(M5:N50)
B5:B15B5=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:C15C5=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:G15G5=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:J15J5=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:M15M5=CONVERT(J5,"ft","m")
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
I made a cheat sheet of 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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top