New to Excel really need help
 

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

New to Excel really need help
 

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:B1000 2=B2)*(Sheet1!C2:C10002=C2)*(Sheet1!D2:D10002=D2)* Sheet1!F2:F10002)

--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"LostInNY" wrote in message
...
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