Count Unique Values In One Column Basis Unique Values in Another Column

Hi All!

I have 6000+ rows of data and i really need help with this:

Column A - Date
Column B - Country

For every unique date, I want to calculate the number of countries visited without any double counting. The result will be returned in a third column (C). Example below:

A B C
20 Mar 2012 Singapore 3
20 Mar 2012 Malaysia 3
20 Mar 2012 China 3
03 Mar 2011 India 2
03 Mar 2011 USA 2
02 Apr 2007 USA 1
02 Apr 2007 USA 1



Is there any way I can do this either with macros or not? Coz I am quite new to excel and If- Then/Loops/Select Case are running around my brain madly with no solutions!

All help will be greatly appreciated!! Many thanks in advance!!

Hello..
Pls provide sample data file...

Hi jraj1106!

Here's the sample data file! :D

In the previous post you said that there is date and country columns.
But in this I can't find them...

Hi again!

In my first post i said 'date' and 'country' as a simplified example. The excel I gave consists of the exact data I am working with. 'Voyage' corresponds with 'Date' and Load Berth' with 'Country'.

Sorry for the confusion!

Hi
Try this macro:

ExcelHelp.xlsm


Regards...

Hi!

Thank you very very much for taking the time to write up that macro for me, first macro i've copied and paste into excel successfuly!

However, is there a way such that every voyage entry will be reflected instead of just one of each? For example there are 10 entries of 'R 121511', can all 10 of them be reflected with value of 4 next to it instead of just 1 of the entries?

Hi
Try this:

ExcelHelp.xlsm

If the probs solved then pls click the star below this post...

Regards...

Hi again!

Thank you very much for your help! the 2nd code is working a little different from what i'm hoping but i think i will just go with the first one you provided and work from there. Again, a million thanks!! have a good weekend ahead :D