VBA Count Unique Values

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

Using an example I've discovered here: https://uk.answers.yahoo.com/questio...8105107AAt5dg4 I'm trying to put together a script which counts the unique values in column B, but assigns these to the unique values in column D.

I must admit I don't entirely understand the original script in full, but I've managed to make some changes which you'll find in the attached a file, which I'm also hoping will perhaps illustrate what I'm trying to achieve a little better.

• Please open the file where you'll be taken to the "In Flight Projects" sheet, then please

• Click on the button,

• You'll notice that from cell G3 the unique values from column D are listed, and in column H, the number of times that value appears in column D.

What I'm trying to do is still keep the values in column G exactly the same, but rather than counting the values from column D, I want to count the unique values from column B.

To show the results I'm looking to achieve and location on the sheet I've included a "Desired Outcome" sheet.

I just wondered whether someone could possibly look at this please and offer some guidance on how I may achieve this.

Many thanks and kind regards

[nilem]

Hi hobbiton73,
try

Please Login or Register  to view this content.

[hobbiton73]

Hi @nilem, thank you very much for taking the time to reply to my post and for the solution which works great.

May I just ask a couple of more things please.

• Would it be possible to paste the results on the "Desired Outcome" sheet in columns B and C starting at row 8 please, and

• Could you possibly insert some notes into the code please so I can learn from this.

Many thanks and kind regards

[nilem]

try again

Please Login or Register  to view this content.

[hobbiton73]

Hi @nilem, thank you very much for taking the time to come back to me with this, it's works great.

Could I possibly trouble you to include some notes the code please, so I can learn how this works.

Many thanks and kind regards

[hobbiton73]

Hi @nilem, I'm very sorry to, trouble you again, but I wonder if you may be able to help me with a problem I've come across when trying to implement the code in live environment.

When I have a list of values in column B on the "In Flight Projects" which currently runs to 160 rows, I recieve the following error:

'Run time error '13': Type mismatch' and highlights this line as the cause :
Sheets("Slide 1").Range("B8:C8").Resize(.Count) = Application.Index(.items, 0, 0)

I've tried to narrow down the cause, and I can't dertermine whether it's the number of characters per value or the total number of characters within the column.

Have you any ideas please where I may be going wrong?

Many thanks and kind regards

[nilem]

Could you show your file in which an error occurs

[hobbiton73]

Hi @nilem, thank you very much for taking the time to come back to me with this, I really appreciate it

Unfortunately I'm unable to post the file because of the confidential information contained within it. I know this isn't particularly helpful.

I've perfomed more tests, but to be honest I really can't determine why it's happening

Many thanks and kind regards

[hobbiton73]

Hi @nilem, thank you very much for your continued help with this.

I've sent you a private message but I'm not sure whether it's work.

Just in case, I've managed to put together a file which illustrates the error.

• If you could please open the file and click the button you will see the error

• However, if you then change the values in column B on rows 11 and 12 to "Digitalisation", and then select the button again, the macro will work.

I'm not sure whether it's a memory buffer or number of characters issue.

For example if you reload the file and change the value in column B on the final role to "Universal" the macro will work.

I hope this helps.

Many thanks and kind regards

[nilem]

Here's your file. try again (I hope everything will turn out now )

Please Login or Register  to view this content.

[Leith Ross]

Hello hobbiton73,

Here is another method of counting and listing the unique values. This method is not sensitive to punctuation characters and also ignores blank rows. This macro has been added to the attached workbook.

Please Login or Register  to view this content.

[hobbiton73]

Hi @Leith Ross, thank you very much for taking the time to reply to my post and for putting the code together.

I've downloaded the file uyou kindly provide, but unfortunately, perhaps as you've been putting this together the values from column D have been overwritten.

If you perhaps have a moment please, you'll see that the original file contained a value in this column, which I then wanted to use on the "Desired Outcome" sheet against the number of times there was a unique entry in column B.

Many thanks and kind regards

[Leith Ross]

Hello hobbiton73,

It would appear that I failed to save my last change before I posted the workbook. Here is the revised macro and workbook.

Revised Code

Please Login or Register  to view this content.

[hobbiton73]

Hi @Leith Ross, thank you very much for taking the time to come back to me with this.

I've downloaded the file you've kindly provided and unfortuately there is no change to the previous one you posted.

I have however tried your code in my live sheet and unfortuantely this doesn't quite provide the desired results. Rather than listing the unique entries from column D, the code lists the values from column B.

Many thanks and kind regards

[Leith Ross]

Hello hobbiton73,

It is easy to change the source parameters. There are three: Worksheet, Starting Cell of the data, and the Column Header. the variable names are Wks, Rng, and Field respectively.

To sort on column D instead of B, the Fieild needs to be changed to match column D header.

If the data does not start at B7 then the variable Rng must be changed.

If you don't feel you can make the needed changes or have problems doing so then post the workbook you will actually be using. I will make the changes for you.

[BS Singh]

Hi Nielm, I too applied your code with given spreadsheet and worked well.
Would you mind applying comments to your code. Particularly for below two lines:

Please Login or Register  to view this content.

Thanks for your time.

Regards,

[hobbiton73]

Hi @BS Singh, thank you for taking the time to look at my post.

If it helps, although I too am looking forward to see the code annotations by @nilem, please find the full working code below:

Please Login or Register  to view this content.

Many thanks and kind regards

[hobbiton73]

Hi @Leith Ross, thank you for taking the time to come back to me with this and for the clear explanation.

I fell comfortable in making the changes.

If it help, I was also fortunate to receive an alternative working solution from @nilem which I've posted previously.

Many thanks and kind regards