Remove all but one instance of a value in one column based on records in another colu

Hopefully the post title makes sense.

I have some vba which returns a result set from a sql database.

The sales data has each line from a customer order (with the order number in column B) Now depending on the size of the order there may be ten or more lines. Each record in the database has the shipping cost allocated to it for the whole order. The query returns hundreds of sales orders into a table.

So if one particulat order was 10 lines and the total shipping cost was 100, then each of the ten lines will have the 100 value against it.

I am trying to figure out a way to remove all but one of the shipping costs for any given sales order. As i mentioned the sales order number is in column B.

Hope someone can point me in the right direction

thanks in advance

In your sample - the implication is that L2:L5 are the same transaction yet each has a different order number - is that an oversight ?

So to be clear - the aim is to alter the duplicate transports such that they become 0, correct ?
If on the other hand you're removing the duplicate rows physically would this not distort the other values like Amount, Quantity etc which are unique to each line ?

I guess it would help if we knew how these values are to be used ? Why do you need to remove the duplicates etc...

For ex. =AVERAGEIF(L:L,B:B,<order no>) would give you the Average Shipping Cost for a given Order which would be correct obviously (SUM would not).

(pending answers to above it might be worth amending the SQL directly...)

I confess I've not the time to play around with the Pivot to see if you could use the Average in a Calculated Field.

If you opt for the new field then:

Please Login or Register  to view this content.

this will apportion the cost such that when the order is aggregated it equates to total cost
(the above assumes as inferred that all order lines are collated together)

edit:
I should have added that if you really wanted to 0 rather than apportion across duplicates then change M1 to 0 and L2/COUNTIF() to just L2