Formula help

Lyndz13

New Member
Joined
Feb 27, 2019
Messages
1
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
 

Some videos you may like

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
Joined
Jan 14, 2014
Messages
303
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.
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top