how to display only unique values

**zazzz** · 11-02-2012, 09:36 AM

in one of my spreadsheets i have a column w/ ID numbers, but many of them are duplicates. some only have one or two dupes while some have hundreds. i'd like to display all the unique values. i dont want to replace the original list though, so i'd want this maybe a few rows below. how would i do this?

once i do this, i'd also like to combine them all in a string. so for example if i have "123", "456" & "789" I'd like a string to say "123, 456, 789". I know how to combine values into a string but this woud have hundreds of values and it'd be a pain to have to do it the traditional way (select one cell, type &", "& then choose another cell, etc).

thanks!

**zbor** · 11-02-2012, 09:38 AM

Can you upload example workbook with your inputs and desired output?

**zazzz** · 11-02-2012, 09:47 AM

OK here you go. This is just one I made up so its not the exact data, but same concept.

**simeon.hruby** · 11-03-2012, 02:53 AM

I am having just about the same exact problem at work. The only way that I have been able to do it so far is to record a macro
that runs the Advanced Filter (in place) then the macro copies the data to another location on let's say "Sheet2". The only issue that I am having is that it does not work every time I run it. It seems that its only allowing me to get the unique values once and that's it....
Other than that I am completley lost on how to solve this one.

Simeon

**zbor** · 11-03-2012, 03:36 AM

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

**zbor** · 11-03-2012, 03:38 AM

zazzz, look at here.

You must have 0 in D31.
Also, in E32 is UDF function so if you copy to your workbook you must also copy code (not only function).

**zazzz** · 11-05-2012, 11:42 AM

Originally Posted by zbor

zazzz, look at here.

You must have 0 in D31.
Also, in E32 is UDF function so if you copy to your workbook you must also copy code (not only function).

Thanks man.

2 questions - what's the significance of E31, where there's a red highlighted 0?
When you say copy code not only function, what exactly does that mean, that copying the formula wont be enough and that this is macro related? Not sure if that's what you're saying but if it is, why would that be - the formula should work all the time, no?

On another note I've seen people saying something about putting in a formula by hitting control-shift-enter, how does that work? Do you do it at time of pasting, or after when trying to run, smth else?

**zbor** · 11-05-2012, 01:46 PM

Originally Posted by zazzz

what's the significance of E31, where there's a red highlighted 0?

It's in D31 in my example, but that's not important...
It's related to:

Formula:

Please Login or Register  to view this content.

Significance is that if you remove it it will count first blank cell as unique and it will put 0 at the end (try).
This 0 grant you to have only unique values without that last 0.

Originally Posted by zazzz

When you say copy code not only function, what exactly does that mean, that copying the formula wont be enough and that this is macro related? Not sure if that's what you're saying but if it is, why would that be - the formula should work all the time, no?

Correct. Above formula will work. But next formula:

Formula:

Please Login or Register  to view this content.

is UDF (User Defined Formula) that is not common Excel formula and if you want to use it you must copy code aswell (hit ctrl+F11 to see code).

Originally Posted by zazzz

On another note I've seen people saying something about putting in a formula by hitting control-shift-enter, how does that work? Do you do it at time of pasting, or after when trying to run, smth else?

It's called array formula. You must hit ctrl+shift+enter (CSE) when you entering formula and you will see curely brackets {=formula} around it.
It's used when you working with array, and not single value.
For example, in this example:

Please Login or Register  to view this content.

First formula, with normal enter, will return value 2 (=2*A1)
Second (looking same) entered with CSE will return 20 (=2*A1+2*A2+2*A3+2*A4) because ti will work with all array (try).

Hope it's clearer now.

**zazzz** · 11-05-2012, 01:49 PM

great, thanks so much for all this

how to display only unique values

LinkBack

Thread Tools

Rate This Thread

Display

how to display only unique values

Re: how to display only unique values

Re: how to display only unique values

Re: how to display only unique values

Re: how to display only unique values

Re: how to display only unique values

Re: how to display only unique values

Re: how to display only unique values

Re: how to display only unique values

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions