Kshitij_Pune
New Member
The Following file for Better Presentation is also available with Google Docs at following Link:
https://docs.google.com/spreadsheet/ccc?key=0AvHakw8hr1sjdGdWeV9qanoxWTdpRmNPSTRWMW5IX3c
How Should I Knock Off the Following
A B C D E F Total
I (100) (200) (300) (400) (100) 200 (900)
II (500) (400) 400 (500) 300 (700)
III (700) (500) 100 200 100 (500) (1,300)
IV (200) (600) (50) (850)
V (500) 8,000 (600) 1,000 (100) (400) 7,400
Total (1,800) 6,700 (400) 200 (600) (450)
The data in above table can be in any order or even there can be blanks.
What I need….?
A B C D E F Total
I (100) (200) (300) =-400+100 =-100+100 =200-200 (900)
II (500) =-400+200 =400-400 =-500+500 =300-300 (700)
III (700) (500) =100-100 =200-200 =100-100 =-500+100+200+100 (1,300)
IV (200) (600) (50) (850)
V =-500+500 =8000-500-100 =-600+500+100 =1000-400-100-500 =-100+100 =-400+400 7,400
(1,800) 6,700 (400) 200 (600) (450)
1. In the first row, I check for a positive number and set it off with the previous column, i.e. I will knock 100 out of 200 with column E and remaining 100 with column D and make them both reduce by that same amount and zero for Column F
2. I will do the same above procedure for Cloumn F in this row also, however I also knock off Column C with Column E and reduce it by that much amount and remaing amount of column C of 200 will be knocked off with that of Column B
How It should Appear
A B C D E F Total
I (100) (200) (300) (300) - - (900)
II (500) (200) - - - (700)
III (700) (500) - - - (100) (1,300)
IV (200) (600) (50) (850)
V - 7,400 - - - - 7,400
(1,300) 6,300 (300) (900) - (150)
Can this be done with combination of some formulas….? Or I would require a MACRO….?
The data that I want to apply this runs into some 5000 line items..
Thank you in advance
https://docs.google.com/spreadsheet/ccc?key=0AvHakw8hr1sjdGdWeV9qanoxWTdpRmNPSTRWMW5IX3c
How Should I Knock Off the Following
A B C D E F Total
I (100) (200) (300) (400) (100) 200 (900)
II (500) (400) 400 (500) 300 (700)
III (700) (500) 100 200 100 (500) (1,300)
IV (200) (600) (50) (850)
V (500) 8,000 (600) 1,000 (100) (400) 7,400
Total (1,800) 6,700 (400) 200 (600) (450)
The data in above table can be in any order or even there can be blanks.
What I need….?
A B C D E F Total
I (100) (200) (300) =-400+100 =-100+100 =200-200 (900)
II (500) =-400+200 =400-400 =-500+500 =300-300 (700)
III (700) (500) =100-100 =200-200 =100-100 =-500+100+200+100 (1,300)
IV (200) (600) (50) (850)
V =-500+500 =8000-500-100 =-600+500+100 =1000-400-100-500 =-100+100 =-400+400 7,400
(1,800) 6,700 (400) 200 (600) (450)
1. In the first row, I check for a positive number and set it off with the previous column, i.e. I will knock 100 out of 200 with column E and remaining 100 with column D and make them both reduce by that same amount and zero for Column F
2. I will do the same above procedure for Cloumn F in this row also, however I also knock off Column C with Column E and reduce it by that much amount and remaing amount of column C of 200 will be knocked off with that of Column B
How It should Appear
A B C D E F Total
I (100) (200) (300) (300) - - (900)
II (500) (200) - - - (700)
III (700) (500) - - - (100) (1,300)
IV (200) (600) (50) (850)
V - 7,400 - - - - 7,400
(1,300) 6,300 (300) (900) - (150)
Can this be done with combination of some formulas….? Or I would require a MACRO….?
The data that I want to apply this runs into some 5000 line items..
Thank you in advance