Extracting unique values from a column

Hi All

I'm using this formula to extract and list unique values from column F

=IF(COUNTIF(F$3:F3,F3)=1,F3,"")

It does the job , and lists the first occurrence of each item. Unfortunately it leaves blank cells where there is a repetition of the item.

So instead of

Susan
John
Harry
Joanne
Billy

It gives

Susan

John
Harry

Joanne
Billy

Where Susan and Harry are repeated.

Can someone think of a way to avoid the blank cells? Or some other (better!) way of making a unique list form the entries in column F? Maybe some VBA under the workbook tab? If it were able to be sorted alphabetically too that would be a real bonus.

Grateful for any help.


Very Best Wishes ,

Change the formula (assumed to be in G3) to this:

=IF(COUNTIF(F$3:F3,F3)=1,MAX(G$2:G2)+1,"")

then copy down. Then you can use this formula to get the unique names without spaces (e.g. in H3):

=IFERROR(INDEX(F:F,MATCH(ROWS($1:1),G:G,0)),"")

Copy this down until you start to get blanks.

Hope this helps.

Pete

Give this macro a try (it outputs to Column G starting on Row 3 - change red highlight if different output location is desired)...

Please Login or Register  to view this content.

Originally Posted by jindon

If not header, give header in F2 and use AdvancedFilter with True in unique protocol, so no need of Formula or VBA.

If VBA

Please Login or Register  to view this content.

You might want to add some code to sort your output as the OP said "If it were able to be sorted alphabetically too that would be a real bonus."

Hi .

Thanks for these.

I assume each should be placed in the code section under the worksheet tab.

I'm afraid none gave any reaction to the data. They didn't give errors either , but none generated the unique list in column O based on the data in column F.

I'm grateful in any case for your help.


Very Best Wishes ,

Originally Posted by CDandVinyl

I'm afraid none gave any reaction to the data. They didn't give errors either

What is in Column F... constants or formulas? If formulas, can you post the one in Cell F3 for us?

Then you will need to upload a sample workbook that doesn't work.

Hi

The values in column F are all constants. I'm trying to send a sorted unique list to O3 down.

Thanks for your help.

Very Best Wishes ,

Originally Posted by CDandVinyl

The values in column F are all constants. I'm trying to send a sorted unique list to O3 down.

Then you will need to heed jindon's suggestion and post a sample workbook for which the codes posted so far do not work so we can see what is different about your actual data compared to the samples we construct here to test our code on.

HI

OK I've attached a sample. Hopefully this will help.

Thanks again.

Very Best Wishes ,

OK thanks for getting back. Yes it may be down to version incompatibilities.

Something which may be blocking our codes from running fully or correctly... the worksheet you posted is password protected. I am not sure what protections you have engaged, but I was able to write out a list to the worksheet but the protection would not let me sort it. Try unprotecting the worksheet and then try running our codes again and see if they now work.

I overlooked the protection when I sent the sample file , so apologies for that.

I did try with an unprotected sheet , with no joy. I still get this 'automation error' message. I'll attach the unprotected sheet.

Here is an alternate macro that I have developed which I think (hope) will work for you. Output is to Column O starting at Row 3 (see last With statement if you need to change this)...

Please Login or Register  to view this content.

Since posting my reply in Post #2 you have confirmed that you are using XL2003, and that you wanted the list of unique names to be sorted. You have also attached an unprotected file in Post #17.

My original formulae will not work for you, as IFERROR was not available in XL2003, so with this latest file of yours I can show you how you can achieve your objective using formulae (if you are still interested in that), as you seem to be running into difficulties with the various VBA solutions that have been offered.

I put this formula in cell N3 of your latest file:

=IF(COUNTIF(F$3:F3,F3)=1,MAX(N$2:N2)+1,"")

and copied down to the bottom of your data. This produces a simple sequence for unique records, each time a new name is encountered in column F. Those unique names can be extracted using this formula in O3:

=IF(ROWS($1:1)>MAX(N:N),"",INDEX(F:F,MATCH(ROWS($1:1),N:N,0)))

and this only needs to be copied down until you start to get blanks. Of course, this is not sorted, but another helper column can be used with this formula in cell P3:

=COUNTIF($O$3:$O$37,"<="&O3)

Copy this down to the bottom of your unique_names list, and it will give a series of numbers which represent the sort order for those names, and you can get the sorted list with this formula in Q3:

=INDEX(O:O,MATCH(ROWS($1:1),P:P,0))

Again, copy this down as required.

This should work in XL2003, as the attached file demonstrates.

Hope this helps.

Pete

Change your current code to

Please Login or Register  to view this content.

I am not sure it is what you want (it's a macro, not event code), but I am wondering if you saw the new code I posted in Message #18 yet or not?

Is the worksheet protected? what is the password?

I have to go out now, so if the worksheet is password protected, you need to unprotect first...
Also tidied up.

Please Login or Register  to view this content.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of any post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

why dont you just copy the column of names onto another tab and use the remove duplicates option under data?

I don't think that Remove Duplicates was available in XL2003.

Pete

Ok thanks for all your help. All is running well now.

You are welcome and thanks for the rep.

I have an additional question to this topic , relating to extracting unique values.

In my workbook I have 5 sheets 2017 - 2021. In column F from F3 on each sheet I list constant values.

I was wanting to open a 6th sheet ('Unique values') and list from B3 a unique list from column F from F3 down of the 5 worksheets , and then in C3 put how many times each value appears.

These would be unique from column F from all the sheets. The sheets are password protected. The other sheets also have change event code.

Can this be done with an event procedure under the tab on the new 'Unique values' sheet?

Grateful for your advice.

Hi Jindon

Many thanks for this. I applied the code with no effect , I'm afraid. It doesn't give errors , but had no impact. I suspect I'm not applying it correctly. I'll attach a sample file for you to see. I've removed all but the most relevant data , but do let me know if you need more.

Thanks again.

Are the Passwords for all sheet common?

1) Delete the current codes in sheet 2017 - 2021 code module.
2) Double click on "ThisWorkbook" in Project pane on the left and paste the following code.

Please Login or Register  to view this content.

Hi

ok thanks very much for this. This now generates the unique list in the 'Unique values' sheet. Unfortunately , it has stopped updating lists in column O on the other sheets.

I do also find 'protected sheet' errors when trying to enter dates in sheets 2017-2021.

I'll attach a fuller sheet with the code above in place so it's clearer..

Grateful for you advice.

OK, hope this works as you expected.

Please Login or Register  to view this content.

Yes - excellent. Thanks Jindon. You are clearly very expert.

I did try to add a count of occurrences for each name in column b of the Unique values sheet

{=SUM(COUNTIF(INDIRECT(ROW($2017:$2021)&"!F3:F500"),B3))}

It works , but it trips the routine up. Never mind.

I wonder if a simple macro activated on opening the Unique values sheet would be the answer after all. It would list the unique names from B3 and count occurrences or each from c3.

Anyway , I'm really grateful for your time and expertise.

OK, I shut down my pc already, so will add the code for it tomorrow.

Ok thanks Jindon.

No formula, simple loop with dictionary object to count...

Please Login or Register  to view this content.

Excellent - working perfectly now. Thanks.

You are welcome and thanks for the reps.