Referencing to other cell

[hadoka]

Good Morning Everybody

I have a question, I have a table with column contains vessels name(B), and
other column contain vessel the agent name for every vessel (C),Sometime
vessel call our Port Two or three times a month and also some time with
different agent .

See Example Below

A B C
Date Vessel Agent
11/11/2006 Mercury 1 Sphinx
15/11/2006 Sea Eagle Gulf
16/11/2006 Mercury 1 Barwil
28/11/2006 Sea Star Barwil
30/11/2006 Sea Star Barwil


I had counted how many time the vessel call the port, But I Need to make
other cell telling which agent the vessel had, and if it called the port
more than one time in a moth with different agent to add the agents beside
each other separating them with a comma "," . Is it Possible to do that by
Excel ????.

That's all... Can anyone one help me ???
---
Best Regards

Mohamed Hadoka
Sphinx Shipping Agency
IT Manager & Shipping Executive
Email : (e-mail address removed)
Msn Messenger : (e-mail address removed)
Yahoo ID : Hadoka1982

 

[Roger Govier]

Hi Mohammed

One way, though it will not shows names in a cell separated by comma,
would be to use a Pivot Table.
First define a Dynamic range for the data.
Insert>Name>Define>Name Mydata Refers to
=OFFSET($A$1,0,0,COUNTA($A:$A),3)
The data range will grow automatically as you add more rows to the
Table.
Then, Data>Pivot Table>Next>Range> Mydata>Finish

Drag Date to the Row area. Click on the Date field
From the PT toolbar which will now be visible, select from the Dropdown
Group & Show Detail>Group>Choose Group by Month.
Drag the Vessel field to the Row area, placing it to the right of the
Date field.
Drag the Agent field to the Column Area.
Now, drag the Vessel field again to the Data area where it should show
Count of Vessel.

For more information on using Pivot Tables take a look at the following
sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/flashfiles/pivot1.html

http://www.edferrero.com/Tutorials.aspx