Extracting Unique Values

[BDNH]

How can I extract unique values from a given range that includes more than 1 column.

For example, I need to extract all the unique values in
H2:K31 there are some empty cells is this range. I need to list the extracted values in 1 column.

can someone give me some ideas please.

Thanks

 

[KenWright]

Given that you have such a small data range, it would be as easy to simply copy each of the 4 columns to a single column, one under the other. Give the col a title, then select all the data and do Data / Filter / Advanced Filter / Select 'Copy to another location' (Then click on a cell in an empty col) and also select 'unique values only'. Hit OK and you should then have a filtered list of unique items.

Regards
Ken...............

 

[acron]

Use Data, Filter, Advanced, which gives you the option to restrict the filtered data to unique values and allows you to extract these values to a new location.

Reda the Help file if you need more assistance.

A.C.

 

[BDNH]

Thanks for both your replies

Ken - In the example I gave the data range is small. What if in another situation it is a large range? Is there any way to create a named range with many columns, filter it, and extract all the unique values to one column?

acron - I tried the advanced filter but it would always extract all the unique values in each column. I couldnt get it to extract all the values in only one column.

Any other suggestions?

Thanks

 

[BDNH]

I still have not been able to figure this out.

Is this possible without copy and pasting the columns manually?

 

[BDNH]

Anyone?

 

[BDNH]

If this can't be easily done will someone please let me know

thanks

 

[DaleWatson123321]

BDNH,

I believe this can be easily done.

If you'd like help, please email me an example. I'll then modify and return the file.

Regards, ...Dale Watson
HOME: nd.watson@shaw.ca

 

[KenWright]

Hi BDNH, sorry about the pause, but bear in mind that no-one gets paid to be here, so it is a hit and miss affair thing as to when people log in. People don't necessarily sit here simply waiting for questions to pop up, but do their best to answer questions when they do. That having been said, it can be done with VBA, but as Dale has offered I would take him up on it.

Regards
Ken............

 

[PepeLeMokko]

Also check this one out :

http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates

 

[BDNH]

Thank you all for your responses.

Dale – I am sending you an example of what I need – thanks

Ken – I am always grateful for the help received on Tek-Tips by the people who take time to answer questions from the desperate people like myself. Please don’t take my repeated insistence personally. I am well aware that people are not paid to help and that they have many other things to do besides log onto Tek-Tips. This was the main reason for my added comments to the original question. Knowing that you were probably busy it was merely an effort to maintain the thread near the surface so that it would not get lost. This would give an opportunity for others to see it and respond freeing you from any sense of obligation (though there be none) from continuing the thread.

I hope you understand that these repeated questions were not directed at you. I thank you for your help and look forward to future help also, I am sure I will need it. It is people like you, that are willing to take time to answer, that make Tek-Tips so successful.

BDNH


PepeLeMokko – Thank you for your link, there is a great deal of useful information. I wonder if once the unique values are highlighted, in a many column range,if they can be extracted to a single column using the highlight as criteria? I will look into this. Thanks

 

[KenWright]

LOL - far from being *busy* I was dying gracefully in my bed for a while from flu like symptoms.

Regards
Ken..............

 

[DaleWatson123321]

BDNH,

As you've probably noticed, I've been busy for the past while. My duties at work have changed, no longer permitting me the time to get involved to the same degree as before.

However, I managed to find the time recently to develop a solution for the file you emailed me. If you can still use the solution, please email me, and I'll then send you the file. With my new duties, I was forced to remove all email... thus I no longer have your address.

After tomorrow, May 16th, I'll be away on vacation for a couple weeks, so hopefully you can email me between now and tomorrow.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca