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

1. ## 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!!

2. ## Re: Count Unique Values In One Column Basis Unique Values in Another Column

Hello..
Pls provide sample data file...

3. ## Re: Count Unique Values In One Column Basis Unique Values in Another Column

Hi jraj1106!

Here's the sample data file! :D

4. ## Re: Count Unique Values In One Column Basis Unique Values in Another Column

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

5. ## Re: Count Unique Values In One Column Basis Unique Values in Another Column

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!

6. ## Re: Count Unique Values In One Column Basis Unique Values in Another Column

Hi
Try this macro:

ExcelHelp.xlsm

Regards...

7. ## Re: Count Unique Values In One Column Basis Unique Values in Another Column

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?

8. ## Re: Count Unique Values In One Column Basis Unique Values in Another Column

Hi
Try this:

ExcelHelp.xlsm

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

Regards...

9. ## Re: Count Unique Values In One Column Basis Unique Values in Another Column

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1