Combining two separate formulas

Status
Not open for further replies.

Kurtin

New Member
Joined
Jan 20, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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.

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
ABCDEF
1Group #Racer 1Racer 2Pair #Total Length Ran (ft)Laps
21MarkTrevor#125001
32AdamMatt#230001
43CarlMaddy#320001
54BryanSteve#420001
61ClaudiaGladdis#515001
72OliviaTerrance#620001
83BorisHelen#710001
94SamRobert#830001
101JordanWilliam#925001
112BlakeBrody#1030001
123IzzyErin#1115002
134BrynCatherine#1220002
141CaliMaya#1320001
152MiaBri#1420001
163AliceLauren#1520001
174BillKate#1620001
181LenaElizabeth#1715001
192BethCourtney#1810001
203HaileyRena#1920001
214LanaBert#2015001
221ChrisBob#2115001
232LarryBernie#2210001
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
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
Destination Sheet
Cell Formulas
RangeFormula
A1A1=COUNTIF('Reference Sheet'!$A$2:$A$23,'Destination Sheet'!I1)
I2I2=SUM(M5:N50)
B5:B14B5=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:C14C5=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:G14G5=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:J14J5=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:M14M5=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 :)
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,886
Office Version
  1. 365
Platform
  1. Windows
Duplicate to: Combining two formulas that work perfectly on their own, but I need the result in the same cell/column

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,122,437
Messages
5,596,130
Members
414,043
Latest member
thomas Stein

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
Top