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

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 referenceView attachment FCS doubt.xlsb

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.

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

JeffreyWeir said:

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.

Click to expand...

Can you upload a sheet showing how you expect everything will look after the macro has run?

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


5if 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

View attachment result.xlsx

JeffreyWeir said:

Can you upload a sheet showing how you expect everything will look after the macro has run?

Click to expand...