|
Merge data rows at fixed ratio
I have a requirement to merge a large number of groupings of two lists
(each an Excel worksheet) with differing numbers of rows, at the ratio of their original sizes (number of rows). This is always a fixed ratio, but most often at other than a 1:1 ratio. For example, 200 rows in Sheet A and 100 rows in Sheet B, merged into Sheet C with 300 rows, merged at the 2:1 ratio A:B, as 2 rows from A, one row from B. How would I do that in Excel? Thanks. |
Merge data rows at fixed ratio
Max,
Looks good. Pardon my ignorance, but is this a macro? - AirBoss Max wrote: For example, 200 rows in Sheet A and 100 rows in Sheet B, merged into Sheet C with 300 rows, merged at the 2:1 ratio A:B, as 2 rows from A, one row from B. Here's a crack at this .. In sheet: A data is within A1:B200, eg 1 11 2 12 3 13 4 14 5 15 6 16 etc In sheet: B data is within A1:B100, eg 100 1000 200 2000 300 3000 400 4000 500 5000 600 6000 etc Then in a new sheet: C, Put in A1: =MOD(ROW(),3) Put in B1: =IF($A1<0,OFFSET(A!$A$1,COUNTIF($A$1:$A1,1)+COUNT IF($A$1:$A1,2)-1,COLUMN(A1)-1),OFFSET(B!$A$1,COUNTIF($A$1:$A1,0)-1,COLUMN(A1)-1)) Copy B1 to C1. Then select A1:C1, copy down to C300. Hide away col A. Cols B and C will return the merged data rows from sheets A & B intermingled in the desired ratio of 2:1, viz we'd get: 1 11 2 12 100 1000 3 13 4 14 200 2000 5 15 6 16 300 3000 7 17 8 18 400 4000 9 19 10 20 500 5000 etc -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AirBoss" wrote in message ups.com... I have a requirement to merge a large number of groupings of two lists (each an Excel worksheet) with differing numbers of rows, at the ratio of their original sizes (number of rows). This is always a fixed ratio, but most often at other than a 1:1 ratio. For example, 200 rows in Sheet A and 100 rows in Sheet B, merged into Sheet C with 300 rows, merged at the 2:1 ratio A:B, as 2 rows from A, one row from B. How would I do that in Excel? Thanks. |
Merge data rows at fixed ratio
Max,
Looks good. Pardon my ignorance, but is this a macro? - AirBoss Max wrote: For example, 200 rows in Sheet A and 100 rows in Sheet B, merged into Sheet C with 300 rows, merged at the 2:1 ratio A:B, as 2 rows from A, one row from B. Here's a crack at this .. In sheet: A data is within A1:B200, eg 1 11 2 12 3 13 4 14 5 15 6 16 etc In sheet: B data is within A1:B100, eg 100 1000 200 2000 300 3000 400 4000 500 5000 600 6000 etc Then in a new sheet: C, Put in A1: =MOD(ROW(),3) Put in B1: =IF($A1<0,OFFSET(A!$A$1,COUNTIF($A$1:$A1,1)+COUNT IF($A$1:$A1,2)-1,COLUMN(A1)-1),OFFSET(B!$A$1,COUNTIF($A$1:$A1,0)-1,COLUMN(A1)-1)) Copy B1 to C1. Then select A1:C1, copy down to C300. Hide away col A. Cols B and C will return the merged data rows from sheets A & B intermingled in the desired ratio of 2:1, viz we'd get: 1 11 2 12 100 1000 3 13 4 14 200 2000 5 15 6 16 300 3000 7 17 8 18 400 4000 9 19 10 20 500 5000 etc -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AirBoss" wrote in message ups.com... I have a requirement to merge a large number of groupings of two lists (each an Excel worksheet) with differing numbers of rows, at the ratio of their original sizes (number of rows). This is always a fixed ratio, but most often at other than a 1:1 ratio. For example, 200 rows in Sheet A and 100 rows in Sheet B, merged into Sheet C with 300 rows, merged at the 2:1 ratio A:B, as 2 rows from A, one row from B. How would I do that in Excel? Thanks. |
Merge data rows at fixed ratio
Also seems circular at C:B1.
AirBoss wrote: Max, Looks good. Pardon my ignorance, but is this a macro? - AirBoss Max wrote: For example, 200 rows in Sheet A and 100 rows in Sheet B, merged into Sheet C with 300 rows, merged at the 2:1 ratio A:B, as 2 rows from A, one row from B. Here's a crack at this .. In sheet: A data is within A1:B200, eg 1 11 2 12 3 13 4 14 5 15 6 16 etc In sheet: B data is within A1:B100, eg 100 1000 200 2000 300 3000 400 4000 500 5000 600 6000 etc Then in a new sheet: C, Put in A1: =MOD(ROW(),3) Put in B1: =IF($A1<0,OFFSET(A!$A$1,COUNTIF($A$1:$A1,1)+COUNT IF($A$1:$A1,2)-1,COLUMN(A1)-1),OFFSET(B!$A$1,COUNTIF($A$1:$A1,0)-1,COLUMN(A1)-1)) Copy B1 to C1. Then select A1:C1, copy down to C300. Hide away col A. Cols B and C will return the merged data rows from sheets A & B intermingled in the desired ratio of 2:1, viz we'd get: 1 11 2 12 100 1000 3 13 4 14 200 2000 5 15 6 16 300 3000 7 17 8 18 400 4000 9 19 10 20 500 5000 etc -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AirBoss" wrote in message ups.com... I have a requirement to merge a large number of groupings of two lists (each an Excel worksheet) with differing numbers of rows, at the ratio of their original sizes (number of rows). This is always a fixed ratio, but most often at other than a 1:1 ratio. For example, 200 rows in Sheet A and 100 rows in Sheet B, merged into Sheet C with 300 rows, merged at the 2:1 ratio A:B, as 2 rows from A, one row from B. How would I do that in Excel? Thanks. |
Merge data rows at fixed ratio
"AirBoss" wrote in message
oups.com... Max, Looks good. Pardon my ignorance, but is this a macro? It's a formulas play, not a macro Also seems circular at C:B1. Not sure what's happening over there .. but here's a working sample for illustration / easy reference: http://www.savefile.com/files/344917 Merge data rows from 2 sheets at fixed ratio.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Merge data rows at fixed ratio
Aha. I'll see if I can understand what's going on and why the earlier
try resulted in circular calc at C:B1. Is this calculating the ratio of input file sizes (rows)? Thanks, Max. Max wrote: "AirBoss" wrote in message oups.com... Max, Looks good. Pardon my ignorance, but is this a macro? It's a formulas play, not a macro Also seems circular at C:B1. Not sure what's happening over there .. but here's a working sample for illustration / easy reference: http://www.savefile.com/files/344917 Merge data rows from 2 sheets at fixed ratio.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Merge data rows at fixed ratio
Is this calculating the ratio of input file sizes (rows)?
The suggestion doesn't calculate the ratio -- the ratio is taken as a known param. It melds the rows from the 2 source sheets in exactly the manner desired in "C", viz.: the first 2 rows from "A", followed by the first row from "B", then the 3rd & 4th rows from "A", followed by the 2nd row from "B", and so on .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AirBoss" wrote in message oups.com... Aha. I'll see if I can understand what's going on and why the earlier try resulted in circular calc at C:B1. Is this calculating the ratio of input file sizes (rows)? Thanks, Max. |
Merge data rows at fixed ratio
OK, I see that now.
If the desired ratio was, say, 6:5, how would the C:A1,C:B1,C:C1 logic be changed? C: A1 logic would presumably be MOD 11, but how about the other 2, if we assume a sequential merge with the extra "A" tacked onto the end? Thanks. -AB Max wrote: Is this calculating the ratio of input file sizes (rows)? The suggestion doesn't calculate the ratio -- the ratio is taken as a known param. It melds the rows from the 2 source sheets in exactly the manner desired in "C", viz.: the first 2 rows from "A", followed by the first row from "B", then the 3rd & 4th rows from "A", followed by the 2nd row from "B", and so on .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AirBoss" wrote in message oups.com... Aha. I'll see if I can understand what's going on and why the earlier try resulted in circular calc at C:B1. Is this calculating the ratio of input file sizes (rows)? Thanks, Max. |
Merge data rows at fixed ratio
"AirBoss" wrote:
If the desired ratio was, say, 6:5, how would the C:A1,C:B1,C:C1 logic be changed? C: A1 logic would presumably be MOD 11, but how about the other 2, if we assume a sequential merge with the extra "A" tacked onto the end? We can actually do a similar 6:5 merger from the 2 source sheets: "A":"B", viz. to have it in "C" as: 1st 6 rows from "A", followed by 1st 5 rows from "B", then 2nd 6 rows from "A", followed by 2nd 5 rows from "B", and so on .. Adapt it in this manner .. In "C", Put in A1: =MOD(ROW(),11) (yes, you're right, we use 11 here [as 6+5=11]) Put in B1: =IF(AND($A10,$A1<7),OFFSET(A!$A$1,COUNTIF($A$1:$A 1,1)+COUNTIF($A$1:$A1,2)+COUNTIF($A$1:$A1,3)+COUNT IF($A$1:$A1,4)+COUNTIF($A$1:$A1,5)+COUNTIF($A$1:$A 1,6)-1,COLUMN(A1)-1),OFFSET(B!$A$1,COUNTIF($A$1:$A1,0)+COUNTIF($A$1: $A1,7)+COUNTIF($A$1:$A1,8)+COUNTIF($A$1:$A1,9)+COU NTIF($A$1:$A1,10)-1,COLUMN(A1)-1)) Copy B1 to C1. Then select A1:C1, copy down as far as required (copy down by the total number of rows from "A" and "B"). Hide away col A. Cols B and C will return the merged data rows from sheets: "A" & "B" intermingled in the desired ratio of 6:5. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Merge data rows at fixed ratio
Here's a revised sample to illustrate the 6:5 merger:
http://www.savefile.com/files/347119 Merge data rows fr 2 shts at fixed ratio (6 to 5).xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Merge data rows at fixed ratio
Here's a refinement for the formulas in cols B & C
(a much shorter version using SUMPRODUCT): Put instead in B1: =IF(AND($A10,$A1<7),OFFSET(A!$A$1,SUMPRODUCT(--($A$1:$A1={1,2,3,4,5,6}))-1,COLUMN(A1)-1),OFFSET(B!$A$1,SUMPRODUCT(--($A$1:$A1={0,7,8,9,10}))-1,COLUMN(A1)-1)) Copy B1 to C1. Then select A1:C1, copy down as far as required (copy down by the total number of rows from "A" and "B"). Hide away col A. Cols B and C will return the merged data rows from sheets: "A" & "B" intermingled in the desired ratio of 6:5. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Merge data rows at fixed ratio
Here's a refinement for the formulas in cols B & C
(a much shorter version using SUMPRODUCT): Put instead in B1: =IF(AND($A10,$A1<7),OFFSET(A!$A$1,SUMPRODUCT(--($A$1:$A1={1,2,3,4,5,6}))-1,COLUMN(A1)-1),OFFSET(B!$A$1,SUMPRODUCT(--($A$1:$A1={0,7,8,9,10}))-1,COLUMN(A1)-1)) Copy B1 to C1. Then select A1:C1, copy down as far as required (copy down by the total number of rows from "A" and "B"). Hide away col A. Cols B and C will return the merged data rows from sheets: "A" & "B" intermingled in the desired ratio of 6:5. Revised sample for the above is available at: http://www.savefile.com/files/347409 Merge data rows fr 2 shts at fixed ratio (6 to 5)_v2.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Merge data rows at fixed ratio
Terrific! Thanks, Max.
I'll see if I can pivot off your work to develop a generic X:Y ratio merge. - AB Max wrote: Here's a refinement for the formulas in cols B & C (a much shorter version using SUMPRODUCT): Put instead in B1: =IF(AND($A10,$A1<7),OFFSET(A!$A$1,SUMPRODUCT(--($A$1:$A1={1,2,3,4,5,6}))-1,COLUMN(A1)-1),OFFSET(B!$A$1,SUMPRODUCT(--($A$1:$A1={0,7,8,9,10}))-1,COLUMN(A1)-1)) Copy B1 to C1. Then select A1:C1, copy down as far as required (copy down by the total number of rows from "A" and "B"). Hide away col A. Cols B and C will return the merged data rows from sheets: "A" & "B" intermingled in the desired ratio of 6:5. Revised sample for the above is available at: http://www.savefile.com/files/347409 Merge data rows fr 2 shts at fixed ratio (6 to 5)_v2.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Merge data rows at fixed ratio
You're welcome, AB !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "AirBoss" wrote in message oups.com... Terrific! Thanks, Max. I'll see if I can pivot off your work to develop a generic X:Y ratio merge. - AB |
| All times are GMT +1. The time now is 05:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com