Excell 2003 VBA Code needs

  • Thread starter Thread starter chandra sekaran
  • Start date Start date
C

chandra sekaran

Sheet 1 Data
________________________________________
A B
P.Code Balance Amount
1 100 25000
2 101 1300
3 102 7800
________________________________________


Sheet 2
I have Detailed Bills


A B C


P.Code Bill No. Billwise Details


1 100 A123 1000
2 100 A124 15000
3 100 A125 5000
4 100 A126 8000
5 101 ABC 500
6 101 ABC1 800
7 101 ABC2 500
8 102 Z123 2000
9 102 Z124 800
10 102 Z125 2000
11 102 Z 126 3000
12 102 Z1276 2500


OUT PUT COLUMN D LIKE THIS.............................


A B C D


P.Code Bill No. B. Details A.OUTSTANDING

1 100 A123 1000 0
2 100 A124 15000 12000
3 100 A125 5000 5000
4 100 A126 8000 8000
5 101 ABC 500 0
6 101 ABC1 800 800
7 101 ABC2 500 500
8 102 Z123 2000 0
9 102 Z124 800 300
10 102 Z125 2000 2000
11 102 Z 126 3000 3000
12 102 Z 1276 2500 2500


Column D is Actuls Outstading of my Bills this should be Sheet 1
P. code amount Equals .


P.CODE= 100 Balance is Only 25000 But we have Bill wise details
More than Rs. 25000 . we need Last in first out method Bills only


P.CODE = 101 Balance is only 1300 But we have Sheet 2 Data is
more than 1800 so we need only 1300 down up Bills details only


like this only will come but i dont know code how i start any
one help


Chandru
 
Sheet 1 Data
________________________________________
        A                 B
     P.Code       Balance Amount
1  100              25000
2  101             1300
3  102             7800
________________________________________

Sheet 2
 I have  Detailed  Bills

      A               B                C

   P.Code       Bill No.          Billwise Details

  1 100            A123               1000
  2 100            A124               15000
  3 100            A125               5000
  4 100            A126               8000
  5 101            ABC                500
  6 101            ABC1              800
  7 101            ABC2              500
  8 102            Z123               2000
  9 102            Z124                800
10 102            Z125               2000
11 102            Z 126              3000
12 102            Z1276              2500

OUT PUT COLUMN  D  LIKE  THIS.............................

   A               B           C                    D

  P.Code      Bill No.   B. Details      A.OUTSTANDING

  1 100            A123    1000                   0
  2 100            A124    15000              12000
  3 100            A125    5000                5000
  4 100            A126    8000                8000
  5 101            ABC     500                        0
  6 101            ABC1    800                    800
  7 101            ABC2     500                  500
  8 102            Z123    2000                      0
  9 102            Z124     800                   300
10 102            Z125    2000                   2000
11 102            Z 126   3000                   3000
12 102            Z 1276  2500                  2500

Column  D  is  Actuls Outstading of my Bills   this should be Sheet 1
P. code  amount  Equals .

P.CODE=  100   Balance  is  Only 25000  But we have Bill wisedetails
More than Rs. 25000   . we need Last in first out method   Bills only

P.CODE = 101   Balance  is  only 1300   But we have  Sheet 2 Data is
more than  1800 so we need only 1300  down up Bills details only

like this   only  will come  but i dont know code how i start   any
one help

Chandru
Click to expand...

It is possible to reproduce the results you show using excel formulas
only. I assumed 500 rows of entries on sheet 1 and sheet 2 - if you
need more, just amend the formula accordingly. The formula in cell D2
of sheet2 looks like this:

=IF(A2="","",MAX(0,MIN(VLOOKUP(A2,Sheet1!$A$2:$B$500,2,FALSE)-
SUMIF(A3:A$500,"=" &A2,C3:C$500),C2)))

This formula can just be copied down to fill the rows beneath. What
this formula is doing is:
1. use the vlookup function to determine the balance value from
sheet1.
2. use the sumif function to determine how much of the balance value
from sheet 1 has already been "used" in lower rows on sheet2
3. use the min function to return the lower of (a) how much is 'left'
from sheet1 and (b) how much is needed from column C of sheet2
4. use the max function to remove any values below zero
5. use the if function to just show a blank if there is no entry on
this row of sheet2

I hope this works for you.
David
 
Back
Top