Luk strings, write values / balance, insert rows : wanted VB help
 

Hi Wizards,
I need a simple code needed for the example below:
Range1 (approx 375)
30-01-2006 xx1 220
15-02-2006 xx2 100
Range2 (approx 6000)
15-01-2006 xx1 120
18-01-2006 xx1 60
15-02-2006 xx2 40
now, month & xx1 has to be compared from Range1 into Range2
it is found in 2 rows, so 120 & 60 has to be written next to 120 & 60 again
and a row has to be inserted next to value 60. Then Balance 80
(220-120-60=80) has to be noted in cell next to 220 in range1

Result to like...
Range1
30-01-2006 xx1 220 40
15-02-2006 xx2 40 60

Range2
15-01-2006 xx1 120 120
18-01-2006 xx1 60 60

15-02-2006 xx2 40 40

hope I explained / confused well.

Hoping to get instant reply for this typical rquirement. Thanx a million.

Luk strings, write values / balance, insert rows : wanted VB help
 

Assuming range 1 is rows 1-20, and range 2 is rows 21 to 40, add this
formula to a column alongside range 1

=SUMPRODUCT(--(MONTH($A$1:$A$20)=MONTH(A1)),--($B$1:$B$20=B1),$C$1:$C$20)-SU
MPRODUCT(--(MONTH($A$21:$A$40)=MONTH(A1)),--($B$21:$B$40=B1),$C$21:$C$40)

and copy down.

For range 2, just use

=C21

and copy down

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Eddy Stan" wrote in message
...
Hi Wizards,
I need a simple code needed for the example below:
Range1 (approx 375)
30-01-2006 xx1 220
15-02-2006 xx2 100
Range2 (approx 6000)
15-01-2006 xx1 120
18-01-2006 xx1 60
15-02-2006 xx2 40
now, month & xx1 has to be compared from Range1 into Range2
it is found in 2 rows, so 120 & 60 has to be written next to 120 & 60
again
and a row has to be inserted next to value 60. Then Balance 80
(220-120-60=80) has to be noted in cell next to 220 in range1

Result to like...
Range1
30-01-2006 xx1 220 40
15-02-2006 xx2 40 60

Range2
15-01-2006 xx1 120 120
18-01-2006 xx1 60 60

15-02-2006 xx2 40 40

hope I explained / confused well.

Hoping to get instant reply for this typical rquirement. Thanx a million.

Luk strings, write values / balance, insert rows : wanted VB h
 

Hi Bob,
Thanks for the code, I already got that result by the formula below.
=C1-SUM(IF(MONTH(F1:F20)=MONTH(A1),IF(G1:G20=B1,I1:I20 ,0)))

Except in the subject, I didn't explain where I want you to insert rows.
I want vb code to insert a row for me in region2 if there is a balance to
adjust.. I cannot do this manually for 6000 rows data.

Can you think on this ? pleeeeeeeeeeeeeeeease.



"Bob Phillips" wrote:

Assuming range 1 is rows 1-20, and range 2 is rows 21 to 40, add this
formula to a column alongside range 1

=SUMPRODUCT(--(MONTH($A$1:$A$20)=MONTH(A1)),--($B$1:$B$20=B1),$C$1:$C$20)-SU
MPRODUCT(--(MONTH($A$21:$A$40)=MONTH(A1)),--($B$21:$B$40=B1),$C$21:$C$40)

and copy down.

For range 2, just use

=C21

and copy down

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Eddy Stan" wrote in message
...
Hi Wizards,
I need a simple code needed for the example below:
Range1 (approx 375)
30-01-2006 xx1 220
15-02-2006 xx2 100
Range2 (approx 6000)
15-01-2006 xx1 120
18-01-2006 xx1 60
15-02-2006 xx2 40
now, month & xx1 has to be compared from Range1 into Range2
it is found in 2 rows, so 120 & 60 has to be written next to 120 & 60
again
and a row has to be inserted next to value 60. Then Balance 80
(220-120-60=80) has to be noted in cell next to 220 in range1

Result to like...
Range1
30-01-2006 xx1 220 40
15-02-2006 xx2 40 60

Range2
15-01-2006 xx1 120 120
18-01-2006 xx1 60 60

15-02-2006 xx2 40 40

hope I explained / confused well.

Hoping to get instant reply for this typical rquirement. Thanx a million.

Luk strings, write values / balance, insert rows : wanted VB h
 

So, where there is a value in range 1 to adjust, you want to duplicate the
value in range2?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Eddy Stan" wrote in message
...
Hi Bob,
Thanks for the code, I already got that result by the formula below.
=C1-SUM(IF(MONTH(F1:F20)=MONTH(A1),IF(G1:G20=B1,I1:I20 ,0)))

Except in the subject, I didn't explain where I want you to insert rows.
I want vb code to insert a row for me in region2 if there is a balance to
adjust.. I cannot do this manually for 6000 rows data.

Can you think on this ? pleeeeeeeeeeeeeeeease.



"Bob Phillips" wrote:

Assuming range 1 is rows 1-20, and range 2 is rows 21 to 40, add this
formula to a column alongside range 1


=SUMPRODUCT(--(MONTH($A$1:$A$20)=MONTH(A1)),--($B$1:$B$20=B1),$C$1:$C$20)-SU

MPRODUCT(--(MONTH($A$21:$A$40)=MONTH(A1)),--($B$21:$B$40=B1),$C$21:$C$40)

and copy down.

For range 2, just use

=C21

and copy down

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Eddy Stan" wrote in message
...
Hi Wizards,
I need a simple code needed for the example below:
Range1 (approx 375)
30-01-2006 xx1 220
15-02-2006 xx2 100
Range2 (approx 6000)
15-01-2006 xx1 120
18-01-2006 xx1 60
15-02-2006 xx2 40
now, month & xx1 has to be compared from Range1 into Range2
it is found in 2 rows, so 120 & 60 has to be written next to 120 & 60
again
and a row has to be inserted next to value 60. Then Balance 80
(220-120-60=80) has to be noted in cell next to 220 in range1

Result to like...
Range1
30-01-2006 xx1 220 40
15-02-2006 xx2 40 60

Range2
15-01-2006 xx1 120 120
18-01-2006 xx1 60 60

15-02-2006 xx2 40 40

hope I explained / confused well.

Hoping to get instant reply for this typical rquirement. Thanx a
million.

Luk strings, write values / balance, insert rows : wanted VB h
 

Hi Bob,
Good morning.
You said use =C21 then, copy down
that value is actually the allocation from range1 value 220
Range1 value 220 has to be allocated in range2 4th column, after satisfying
the conditions (month check & xx1 check), if there is balance then a row must
be inserted at last xx1 find in range2 then in that row the balance & xx1 has
to be incorporated in their cells (2nd & 4th). Simulaneously at range1 next
to 220 the balance has to be shown.
Bit urgent, time given to me comes very close, if th's over 75% is over,
rest anyway we have to do manually..
Thinking of 6000 rows doing manually kills me.

"Bob Phillips" wrote:

So, where there is a value in range 1 to adjust, you want to duplicate the
value in range2?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Eddy Stan" wrote in message
...
Hi Bob,
Thanks for the code, I already got that result by the formula below.
=C1-SUM(IF(MONTH(F1:F20)=MONTH(A1),IF(G1:G20=B1,I1:I20 ,0)))

Except in the subject, I didn't explain where I want you to insert rows.
I want vb code to insert a row for me in region2 if there is a balance to
adjust.. I cannot do this manually for 6000 rows data.

Can you think on this ? pleeeeeeeeeeeeeeeease.



"Bob Phillips" wrote:

Assuming range 1 is rows 1-20, and range 2 is rows 21 to 40, add this
formula to a column alongside range 1


=SUMPRODUCT(--(MONTH($A$1:$A$20)=MONTH(A1)),--($B$1:$B$20=B1),$C$1:$C$20)-SU

MPRODUCT(--(MONTH($A$21:$A$40)=MONTH(A1)),--($B$21:$B$40=B1),$C$21:$C$40)

and copy down.

For range 2, just use

=C21

and copy down

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Eddy Stan" wrote in message
...
Hi Wizards,
I need a simple code needed for the example below:
Range1 (approx 375)
30-01-2006 xx1 220
15-02-2006 xx2 100
Range2 (approx 6000)
15-01-2006 xx1 120
18-01-2006 xx1 60
15-02-2006 xx2 40
now, month & xx1 has to be compared from Range1 into Range2
it is found in 2 rows, so 120 & 60 has to be written next to 120 & 60
again
and a row has to be inserted next to value 60. Then Balance 80
(220-120-60=80) has to be noted in cell next to 220 in range1

Result to like...
Range1
30-01-2006 xx1 220 40
15-02-2006 xx2 40 60

Range2
15-01-2006 xx1 120 120
18-01-2006 xx1 60 60

15-02-2006 xx2 40 40

hope I explained / confused well.

Hoping to get instant reply for this typical rquirement. Thanx a
million.

Luk strings, write values / balance, insert rows : wanted VB h
 

Thanks Bob for the support you gave.
I took code from some other thread and we have worked out.
Regards,
Eddy Stan

"Bob Phillips" wrote:

So, where there is a value in range 1 to adjust, you want to duplicate the
value in range2?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Eddy Stan" wrote in message
...
Hi Bob,
Thanks for the code, I already got that result by the formula below.
=C1-SUM(IF(MONTH(F1:F20)=MONTH(A1),IF(G1:G20=B1,I1:I20 ,0)))

Except in the subject, I didn't explain where I want you to insert rows.
I want vb code to insert a row for me in region2 if there is a balance to
adjust.. I cannot do this manually for 6000 rows data.

Can you think on this ? pleeeeeeeeeeeeeeeease.



"Bob Phillips" wrote:

Assuming range 1 is rows 1-20, and range 2 is rows 21 to 40, add this
formula to a column alongside range 1


=SUMPRODUCT(--(MONTH($A$1:$A$20)=MONTH(A1)),--($B$1:$B$20=B1),$C$1:$C$20)-SU

MPRODUCT(--(MONTH($A$21:$A$40)=MONTH(A1)),--($B$21:$B$40=B1),$C$21:$C$40)

and copy down.

For range 2, just use

=C21

and copy down

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Eddy Stan" wrote in message
...
Hi Wizards,
I need a simple code needed for the example below:
Range1 (approx 375)
30-01-2006 xx1 220
15-02-2006 xx2 100
Range2 (approx 6000)
15-01-2006 xx1 120
18-01-2006 xx1 60
15-02-2006 xx2 40
now, month & xx1 has to be compared from Range1 into Range2
it is found in 2 rows, so 120 & 60 has to be written next to 120 & 60
again
and a row has to be inserted next to value 60. Then Balance 80
(220-120-60=80) has to be noted in cell next to 220 in range1

Result to like...
Range1
30-01-2006 xx1 220 40
15-02-2006 xx2 40 60

Range2
15-01-2006 xx1 120 120
18-01-2006 xx1 60 60

15-02-2006 xx2 40 40

hope I explained / confused well.

Hoping to get instant reply for this typical rquirement. Thanx a
million.