Hey guys, I was wondering if anyone knew of a way to pull the unique values out of a column of entries and put them into another column.
For example I'll have a list of maybe 2,000 but with only 1-7 different values in the column, what I want to do is populate another column with only the list of the 1-7 values that appear in the larger set.
Is there any formula that will do that?
How about using the FILTER tools i.e. auto filter or advanced filter
Ed
Because I'm not trying to filter the column with all the information in it, I just need the list of unique values in the column, I dont want the column to change in any way.
Last edited by Rgaherty; 11-12-2007 at 09:44 AM.
Use Advanced filter with these options: "copy to another location" and "Unique records only". This should give you a new list with only the unique values without doing anything to your original list.
Well I tried the advanced filter option and while it works if the data never changes I need it to function with different sets of information. It will be on a blank sheet, when somone puts the information in I need this to update automatically which it doesnt appear to do unfortunately.
If your data is in col A2-A7 then the following example works
Adjust the range to suit your circumstances
Please Login or Register to view this content.
This is an array formula so needs to be entered with CTRL + SHIFT + ENTER
With 2000 rows it will be a bit slow.
Ed
EDIT this will be in the first row, e.g. B2 and then copied down.
If you want it on another sheet then the references would need to reflect the source sheet.
Ed
Last edited by EdMac; 11-12-2007 at 10:43 AM.
Will that work even with text values? That's what all of these are.
Yep,
no problem with text
Ed
Well I cant seem to get it to show a value but I'm most likely using it incorrectly. I'm not really familiar with the functions being used in there. My data is in F5:2005 and I'm trying to place this list in cells in B2008:B2020.
That's what I put into B2008Please Login or Register to view this content.
OK,
As your data starts in row 4, replace the '+1' in the formula with '+4'. Confirm the formula with control + shift + enter - you will see brackets like this {} apear around it. Just hitting enter will not work
Then use the fill handle to drag down as far as you need from B2008
Ed
Hrmm, it's still coming up blank. It looks like there is a #!ref error in place of the 1:1 part of the formula now.
Have you replaced both instances of +1?
Ed
It should look like this
=IF(ISERROR(INDEX($F$5:$F$2005,SMALL(IF(MATCH($F$5:$F$2005,$F$5:$F$2005,0)=ROW(INDIRECT("1:"&ROWS($F$5:$F$2005))),ROW($F$5:$F$2005)-INDEX(ROW($F$5:$F$2005),1)+4,""),ROW(1:1)))),"",INDEX($F$5:$F$2005,SMALL(IF(MATCH($F$5:$F$2005,$F$5:$F$2005,0)=ROW(INDIRECT("1:"&ROWS($F$5:$F$2005))),ROW($F$5:$F$2005)-INDEX(ROW($F$5:F$2005),1)+4,""),ROW(1:1))))
Last edited by EdMac; 11-12-2007 at 12:23 PM.
Yea it looks like:
nowPlease Login or Register to view this content.
It should be this
If there are any gaps in col F, that could cause problemsPlease Login or Register to view this content.
Ed
Well there wont be gaps in the middle of the data but all of the cells may not be populated, the lists that will be entered vary in length.
It does look like the space at the end is causing it, hmm, this proves problematic.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks