Results 1 to 5 of 5

Thread: Excel macro to adjust negative value with positive value on a given criteria

  1. #1

    Excel macro to adjust negative value with positive value on a given criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    GL CC Amount Project Comments
    164001 9311 500.00 12000155 in progress
    164002 9311 800.00 12000156 other rect
    164002 9311 500.00 12000157 to iniate
    164002 9311 500.00 12000158 go live
    164005 9311 500.00 12000159 go live
    164001 9322 1,000.00 12000160 in progress
    164002 9322 1,000.00 12000161 other rect
    164004 9322 (500.00) 12000162 to iniate
    164004 9322 500.00 12000163 go live
    164005 9322 1,000.00 12000164 go live
    164001 9333 2,000.00 12000165 in progress
    164002 9333 500.00 12000166 other rect
    164003 9333 (500.00) 12000167 to iniate
    164003 9333 2,000.00 12000168 go live
    164004 9333 2,000.00 12000169 go live


    hi
    I have a list of data here which contains negative and positive numbers in a list of cc so I need the below steps to be done with macros with some criteria , please help me techs
    Steps :
    1
    first it has to filter the each cc
    2
    then if it finds any negative number the it has to filter the corresponding GL
    3
    then it has to find any positive number in the filtered range with same Gl and cc
    4
    then if the positive number is greater than the negative number then it can adjust with it and delete the negative row but before that it has to copy both and negative row and the positive row I next sheet for reference and then it can do the adjustment, if the credit value is greater then it can be split and adjusted with positive values with criteria same gl and cc
    5
    if it cannot find any positive value with same gl and cc of negative value then it has to leave and go to next negative value and follow step 3
    6
    after all the adjust ment is done then in comments column has to be filtered with "to initiate" and "other rect" then again it should start from step1

    Please let me know if u need more details, and file is attached for referenceFCS doubt.xlsb

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    I don't understand these bits in bold:
    4. if the positive number is greater than the negative number then it can adjust with it - what do you mean by 'adjust with it'
    4. copy both and negative row and the positive row I next sheet for reference - what do you mean by 'I'
    6. after all the adjustment is done then in comments column has to be filtered with "to initiate" and "other rect" then again it should start from step1 - This seems a bit strange...when you first start at step one, the data has not been filtered. But the next time you return to step one, the data is filtered first. I don't think I follow what this step is supposed to do...can you elaborate further.



  3. #3

    reply for the doubt

    hi Jefferey,

    thanks for your interest let me explain you the bold one

    1. adjust with it means the negative number should be adjusted with positive number eg(164001 9366 has -25 and with same criteris 16401 9366 50 then the negative 25 will be adjusted with 50 and and it will show only balance in 164001 9366 25) finally there should be no negative numbers but criteria should be kept in mind while adjusting

    2.sorry that is typo error that is IN instead of I (in next sheet)

    3.sorry it should be step 3

    Quote Originally Posted by JeffreyWeir View Post
    I don't understand these bits in bold:
    4. if the positive number is greater than the negative number then it can adjust with it - what do you mean by 'adjust with it'
    4. copy both and negative row and the positive row I next sheet for reference - what do you mean by 'I'
    6. after all the adjustment is done then in comments column has to be filtered with "to initiate" and "other rect" then again it should start from step1 - This seems a bit strange...when you first start at step one, the data has not been filtered. But the next time you return to step one, the data is filtered first. I don't think I follow what this step is supposed to do...can you elaborate further.



  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Can you upload a sheet showing how you expect everything will look after the macro has run?

  5. #5
    hi jefferry

    please find attached the result file and please make sure of below point while creating macro

    4.then if the positive number is greater than the negative number then it can adjust with it and delete the negative row but before that it has to copy both and negative row and the positive row I next sheet for reference and then it can do the adjustment, if the credit value is greater then it can be split and adjusted with positive values with criteria same gl and cc


    5
    if it cannot find any positive value with same gl and cc of negative value then it has to leave and go to next negative value and follow step 3

    result.xlsx





    Quote Originally Posted by JeffreyWeir View Post
    Can you upload a sheet showing how you expect everything will look after the macro has run?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •