New to Excel really need help

[LostInNY]

I have an Excel file of about 10,000 lines with the following format:

OriginCity OriginState DestCity DestCountry Cost1 Cost2

NY NY Vigo Spain 110
24
NY NY Vigo Spain 110
36
LA CA Hamburg Germany 245 23
LA CA Hamburg Germany 245
23

I need to create another Excel file for each Origin-Dest combination, but I
only need the value from the Cost1 column once and add the Cost2 columns
together. So the new lines should look like:

OriginCity OriginState DestCity DestCountry Cost1 Cost2

NY NY Vigo Spain 110
60
LA CA Hamburg Germany 245 46

 

[Sandy Mann]

With your original data in Sheet1 I would use Advanced Filter to get a
unique set of data in
OriginCity OriginState DestCity DestCountry Cost1

then in F2 enter the formuula:

=SUMPRODUCT((Sheet1!A2:A10002=A2)*(Sheet1!B2:B10002=B2)*(Sheet1!C2:C10002=C2)*(Sheet1!D210002=D2)*Sheet1!F2:F10002)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk