Merge data rows at fixed ratio

A

AirBoss

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

Max

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.
Click to expand...

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)+COUNTIF($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


---
 
A

AirBoss

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

Max

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

AirBoss

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
 
G

Guest

AirBoss said:
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?
Click to expand...

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($A1>0,$A1<7),OFFSET(A!$A$1,COUNTIF($A$1:$A1,1)+COUNTIF($A$1:$A1,2)+COUNTIF($A$1:$A1,3)+COUNTIF($A$1:$A1,4)+COUNTIF($A$1:$A1,5)+COUNTIF($A$1:$A1,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)+COUNTIF($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.

---
 
G

Guest

Here's a refinement for the formulas in cols B & C
(a much shorter version using SUMPRODUCT):

Put instead in B1:
=IF(AND($A1>0,$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.

---
 
M

Max

Here's a refinement for the formulas in cols B & C
(a much shorter version using SUMPRODUCT):

Put instead in B1:
=IF(AND($A1>0,$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

---
 
A

AirBoss

Terrific! Thanks, Max.

I'll see if I can pivot off your work to develop a generic X:Y ratio
merge.

- AB
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

form buttons on sheet collapse on unhide - sometimes 5
SUMPRODUCT QUESTION?, Ratios 3
Merge contents of two worksheets 1
freez issues need merge help 1
How to filter rows data based on background color of individual cells 1
rows and columns on worksheet 1
Automatically adding additional rows 3
Merge Cells- A challenge! 1

Top