Formula help

[Lyndz13]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sales Order

[/TD]
[TD]SO Line
[/TD]
[TD]Initial Budget
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1043228
[/TD]
[TD]1
[/TD]
[TD]3100
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1043228
[/TD]
[TD]2
[/TD]
[TD]3100
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1043228 - Total
[/TD]
[TD][/TD]
[TD]6200
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1046200
[/TD]
[TD]1
[/TD]
[TD]51500
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1046200
[/TD]
[TD]2
[/TD]
[TD]51500

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1046200
[/TD]
[TD]1
[/TD]
[TD]108397.96
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1046200
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1046200 Total
[/TD]
[TD][/TD]
[TD]211397.96
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Good morning excel people Hope you are all well.
I have an excel question that as stumped me.

I have an excel worksheet that shows all our Sales Orders wit their relevant budget. There is multiple SO Lines so i have created a subtotal for each SO. However, the way the orders are set up is that sometimes we have duplicated budgets. Im trying to create a formula that would show these duplications as Green...yet there is so many variables.

I have shown quick table above. I want to highlight 1043228 row 2 as it is duplicating row 1 budget.
I want to highlight row 2 in 1046200 as this is duplicating the budget above.
I cant do Max as in 1046200 the entire budget should be 159k....and its now always Line 1 that has the duplicated budget

I need to be able to create a macro at the end to complete all formula etc and each month the subtotal changes

Any help is appreciated

 

[Col Delane]

Howdy, and welcome to the Forum

Q1. Are you looking to "fix" these duplications by removing them (requires VBA), or just identify them via highlighting (Conditional Formatting [CF] should do it)?

I'm a bit confused by this:

...and its now always Line 1 that has the duplicated budget.

Q2. Isn't it each SO line 2 that duplicates SO line 1 of each order?


The Conditional Formatting solution

Assuming:

1. that each SO line 2 is the one that duplicates the budget value of SO line 1 of each order (always!), and
2. the budget value for SO line 1 of the first order (1043228) is in cell D5

Apply conditional formatting to D5:Dxx

1. Select D5:Dxx
2. Open the CF dialogue box
3. Select "New Rule" followed by "Use a formula to determine which cells to format"
4. In the formula box enter = AND( $B5 = $B4, $C5 = 2 ) [Note that $ are only used before column ref's!!]
5. Click the Format button at bottom right and select your formatting options (e.g Fill = RED)
6. Click Ok twice to save the CF and exit back to the spreadsheet.

The above should apply your selected formatting to the budget value of each SO line 2.