| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Repeating rows not wanted | Excel Worksheet Functions | |||
| Removing rows not wanted... | Excel Discussion (Misc queries) | |||
| Insert Rows Based on values in another worksheet | Excel Discussion (Misc queries) | |||
| Insert Textbox values to seperate rows/columns | Excel Programming | |||
| Insert Rows in Balance Sheet Template | New Users to Excel | |||
Linear Mode
