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.

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.

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.

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.

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

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

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.

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.

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

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

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

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

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

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