Quote Originally Posted by Nurofen
Thanks Charlize


Thats looks great hope I can work it into place with other parts.

Have to try find counting methods.

Any ideas of how to go about it or what i should be searching for cause i'm lost.

I've tried offset but can't get it to count, tried select case and that just lost me.

Thank you for taking time to help

Nurofen
The problem with your list is that you have blanks in a row, it would be simplier when every row has the department name. From there we could make a collection of unique items and for each item in the collection we count the no of items and copy that result to the corresponding sheet (sheet has same name as the item in the collection).

And every department sheet has the mailadresses in A1 and A2

The solution is tested on Excel 2003 (when you are using an older version, i think you have to specifiy a path for saving the temporary file).