Formula help

[Lyndz13]

Sales Order	SO Line	Initial Budget
1043228	1	3100
1043228	2	3100
1043228 - Total		6200
1046200	1	51500
1046200	2	51500
1046200	1	108397.96
1046200	1
1046200 Total		211397.96

<tbody>
</tbody>

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.