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

K

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


Excel Doc for Row Transfers and Repetitions.xlsx
ABCDEF
1Group #Racer 1Racer 2Pair #Total Length Ran (ft)Laps
21MarkTrevor#125001
31AdamMatt#225001
41CarlMaddy#330001
51BryanSteve#430001
61ClaudiaGladdis#530001
71OliviaTerrance#620001
82BorisHelen#720001
92SamRobert#820001
102JordanWilliam#920001
112BlakeBrody#1020001
122IzzyErin#1120001
132IzzyErin#1120001
143BrynCatherine#1220001
153BrynCatherine#1220001
163CaliMaya#1320001
173MiaBri#1415001
183AliceLauren#1515001
193BillKate#1615001
204LenaElizabeth#1715001
214BethCourtney#1815001
224HaileyRena#1915002
234LanaBert#2010002
244ChrisBob#2110002
254LarryBernie#2210002
Duplicated 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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
359
Asbestos_Jen
Asbestos_Jen
S
  • Question Question
Two way look up with mutiple duplicate criteria with mutiple return
Replies
3
Views
197
sanrama
S
Guybrush Threepwood
  • Question Question
Using Filter for Criteria in Multiple Columns and a Specific Row Range?
Replies
0
Views
420
Guybrush Threepwood
Guybrush Threepwood
L
  • Solved
Using CELL function result as part of range in an AVERAGE formula
Replies
3
Views
342
Fluff
Fluff
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
317
dshtesy
D

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

Share this page

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