dark_horizon
New Member
- Joined
- Mar 27, 2014
- Messages
- 15
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sr
[/TD]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]L1
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]L1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]L2
[/TD]
[TD]6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]L1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]L2
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]L3
[/TD]
[TD]8
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]L1
[/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]L1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]L2
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]L2
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]L3
[/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]L4
[/TD]
[TD]15
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]L1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]L1
[/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]L2
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]L3
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]L4
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]L5
[/TD]
[TD]20
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hi All
Above is an example of input data I extract from a database.Column A has multiple levels (named L1, L2, L3, L4, L5). Column B has values. The way it functions is,
L2 = Sum of all L1 above it, till you encounter another L2above.
L3 = sum of all L2 above it till you encounter another L3above.
Similar for L4. The final level is L5 which has sum of all L4 in the column.
Also, sometimes there is a standalone L2 (for which no L1 breakup details are available), and it has to be considered as it is. Example – Cell B10,which has a standalone value of 2, and there is immediately another L2 above it in Cell B9, which is a sum of L1 above it
The issue is sometimes the data is incorrect and an L2 might not be the sum of all L1 above it (like a L1 row might have been missed by the database). I need to verify in column C if such an error has taken place.
So my output in column C should have values same as Column B for L2, L3, L4, L5 if there is no error. Or different values, if there is error.
The range is dynamic and runs into hundreds of rows.
Any macro or pre-existing excel formula can accomplish this?
I would be very grateful for any help/ code or pointers Ican get for this problem. Thank you.
<tbody>[TR]
[TD]Sr
[/TD]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]L1
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]L1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]L2
[/TD]
[TD]6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]L1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]L2
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]L3
[/TD]
[TD]8
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]L1
[/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]L1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]L2
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]L2
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]L3
[/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]L4
[/TD]
[TD]15
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]L1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]L1
[/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]L2
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]L3
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]L4
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]L5
[/TD]
[TD]20
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hi All
Above is an example of input data I extract from a database.Column A has multiple levels (named L1, L2, L3, L4, L5). Column B has values. The way it functions is,
L2 = Sum of all L1 above it, till you encounter another L2above.
L3 = sum of all L2 above it till you encounter another L3above.
Similar for L4. The final level is L5 which has sum of all L4 in the column.
Also, sometimes there is a standalone L2 (for which no L1 breakup details are available), and it has to be considered as it is. Example – Cell B10,which has a standalone value of 2, and there is immediately another L2 above it in Cell B9, which is a sum of L1 above it
The issue is sometimes the data is incorrect and an L2 might not be the sum of all L1 above it (like a L1 row might have been missed by the database). I need to verify in column C if such an error has taken place.
So my output in column C should have values same as Column B for L2, L3, L4, L5 if there is no error. Or different values, if there is error.
The range is dynamic and runs into hundreds of rows.
Any macro or pre-existing excel formula can accomplish this?
I would be very grateful for any help/ code or pointers Ican get for this problem. Thank you.
Last edited: