# Formula help

#### Lyndz13

##### New Member
 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

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Col Delane

##### Active Member
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.

Replies
12
Views
131
Replies
1
Views
68
Replies
1
Views
81
Replies
0
Views
53
Replies
13
Views
90

1,109,446
Messages
5,528,802
Members
409,836
Latest member
karnasrinivas

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...