Excel formula help

Question

Hello All,

I am looking for some guidance on the appropriate excel formula.

I have 2 data tabs of 000s of horizontal data fields and approximately 30 columns of each tab.

First tab is Carrier Shipping, the other dispatched goods tab. The common field in both tabs is the order number

I have used a vlookup to import the shipping costs to the despatched tab, the unfortunate problem I am now faced with is for many orders there are multiple lines of different products which now records the shipping cost for each line propelling the costs.

How do I in these cases only record the shipping cost once on the first line of the order ?

Unfortunately I am unable to remove duplicates as this would mean deleting the record of the despatched products which the data will be used for other tables

Any help or guidance would be greatly appreciated. Many thanks

KR

Mark

Answer 1

Sorry Hans,

It my mistake, I had left off part of the formula. Many thanks for your support

KR

Mark

Answer 2

Hi Hans,

The formula seems to be working, I will spot check the 000s of lines tomorrow.

Duplicated shipping values of the same order number return "false", how can the formula be edited to return either "", or "0".

I have taken a .jpg if you need for reference

Many thanks

KR

Mark

Answer 3

Thank you Hans, I will try this and let you know how I get on

Answer 4

Let's say the order number is in D2 and down on the Despatched Goods sheet.

Use a formula like this for the shipping costs in row 2:

=IF(COUNTIF(D$2:D2, D2)=1, VLOOKUP(D2, ...), "")

This can be filled down.