• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Selecting only one of each values

tazz

Member
Hi,

Col. A has values that repeat 2 or more times.

Col.B needs only one of each of the values from column A.

I tried with Data/Remove duplicates but is not what I want.

Any ideas?

Thank you
 
Hi Jai,

I tried with advanced filtering but for some reason is not returning the right result.

I was thinking perhaps a formula will fix this problem.

Col A has apx 350 data entry(some of them unique), in col B perhaps will be apx.10-12 values.

Thanks
 
Good day tazz


You say you want to remove duplicates but when you run Remove Duplicates you do not get what you want! you have used advances filtering and you do not get the result you need!, you say there are approximately 350 values in column A but when you get the result you want there should only be 10-12 values in column B, must be honest and say I am struggling to understand your requirements
 
Hi Tazz,


Assuming the data in in range A1:A350.

Select the range and click on Data - > Advanced filter.

Select Copy to another location and enter B1 in copy to field.

Leave criteria range blank.

Select Uniue records only.


This should give you the unique records in the list.


Jai
 
Since you are using removing duplicates, please refer to Narayan's advice in this thread:

http://chandoo.org/forums/topic/how-to-remove-duplicate-from-a-integerintegertxt-values


And if it still doesn't give you the results you intend to then check if the data is really the same e.g. sometimes some invisible characters like white space are there which appear to be the same but aren't the same.

"Chandoo_Org"

"Chandoo_Org "

Place them in Excel without quotes and you won't notice a difference.
 
Hi,

Advanced filtering is returning only one value(the first value from column A) and I have this message:"The extract range has a missing or illegal field name".

In column A are only numbers so no special characters.

One more thing: all cells in col. A have associated a formula (I am thinking perhaps this is why the filtering is not working).

Is there a formula that can help me avoid their filtering?


Thank you for helping on this issue.
 
Hi tazz,

Check out a recent response from Kaushik to a similar problem, to obtain a unique listing from a list.

Please search for "combining-of-2-lists" to see the post. The spam filters on this site are preventing my posting of a link.


Since you are not combining two lists, you would only need the first half of the formula.


Cheers,

Sajan.
 
Hi Tazz,


Looks like you do not have a header row. So you are getting this message.

Insert a header row and try again. Later you can delete the header row.


Jai
 
Hi Jai,

I inserted a header row and It's working.

Is there a way to make excel filtering automatic?

My data will change every other day and I saw that the data it is filtered only when I retake the whole process of filtering.

Thank you.
 
Hi Tazz,


I think you would need a macro to do it.

I am not good with macros.

Maybe someone else can help you out here.


Jai
 
Back
Top