• 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.

Extract a unique distinct list from a column in excel

Hi Experts,


I need to get the list of unique distinct list from the coloum where it is expected that coloum will have more than 500K values....while unique distinct list may have only 2K-3K values.


I tried the below mentioned solution and it work fine for smaller ranges (say 100-1K) but as soon as it get a range of more than 100K, this is not efficient.


Formula which I used =INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0)) with (Ctrl+Shift+Enter)


May I have a UDF/Macro/Formula to overcome this? If I use remove duplicate of excel than it do the job in seconds...but it will become manual activity which is not desired.


You are requested to use dynamic range in formula so that it take the minimum time as currently I have around 100,000 value listed in coloum but expected to raise.


Regards,

Kuldeep
 
Kuldeep


Have you tried using an Advanced Filter?

Setup a Criteria Area of the Fields and values

Advanced Filter

Filter to another location

Unique Records only


Refer: http://chandoo.org/wp/2008/06/19/quickly-display-unique-items-in-an-excel-list-using-advanced-filters/
 
Hi,


I could modified the code of NarayanK given few days back to resolve another issue....


While it seems to be working for me, I expect any other better solution to this....or a improvement in this macro....


This is because I never used VB before....and just played with the code available with me.


---------------------------------------------------------------------------------

Public Sub Customer_Update()


Application.ScreenUpdating = False


Dim Copy_From_Sheet As Worksheet

Dim Paste_To_Sheet As Worksheet

Set Copy_From_Sheet = ThisWorkbook.Worksheets("Sheet1")

Set Paste_To_Sheet = ThisWorkbook.Worksheets("Sheet1")

Paste_To_Sheet.Activate

Columns("A:A").Select

Selection.Copy

Range("B1").Select

ActiveSheet.Paste

Application.CutCopyMode = False

ActiveSheet.Range("$B$1:$B$500000").RemoveDuplicates Columns:=1, Header:= _

xlYes

End Sub

------------------------------------------------------------------------------------


Regards,

Kuldeep
 
Hi Hui,


I think using use of Advanced Filter will make it a manual work..


I need to either updated this list automatically or just by pressing a button. This is being feed to somewhere else in project as input...


Regards,

Kuldeep
 
Kuldeep,


Create a Pivot Table with Dynamic Range & Record a Macro for refresh when click on a button or a Worksheet_Activate event.


Create a PT & Keep in a separate sheet & from the PT sheet right click, click on
Code:
View Code & use VBA like,

[pre][code]<br />
Private Sub Worksheet_Activate()<br />
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh<br />
ActiveSheet.PivotTables("PivotTable1").PivotFields("Enter_Header_Here_").AutoSort xlAscending, "Enter_header_Here"<br />
End Sub<br />
[/pre]

This simple code will refresh PT & Sort by Ascending order whenever you activate the sheet. I don't know much about VBA, experts will have more options.


If you change some format of PT, this will same like as ranges. eg; remove Field Headers, Grand Total[/code]
 
Hi Kuldeep ,


Can you clarify the following ?


1. Where do you want to create the unique distinct list ? In a separate worksheet or a different column ?


2. Should the unique distinct list be sorted in any order ?


Narayan
 
Hi NarayanK,


For this instance unique distinct list is needed in different coloum on same sheet but i hope to get this in way where sheet name and coloum name must be present in code so that in case i need to use it to copy on another sheet....i need not to bother you guys...


It is always good to have sorted list but in this case it is not required as this will be feed to your previous solution (Dynamic Search bar in excel from partial input...) which will give me the only values i need.


This is due to my inadequate knowledge to accomplish this VB jobs.


Regards,

Kuldeep
 
Hi Kuldeep ,


I was about to write the code , when I came across this ; the best thing you can do is just copy the code from this link :


http://www.cpearson.com/excel/distinctvalues.aspx


Narayan
 
Hello Narayank,


Thanks for guiding my plane to the right airport. I downloaded the example workbook and really this is what I desired....


But the bad part of it is that I could not change the size of result array used in example file. I have uploaded the file at http://sdrv.ms/LeRKZ2 so that you can understand it easily.


If I changed the input range it works but the output is limited to 10 distinct value as I could not figure out how to expand this.


I think I am missing something related to this part of code.


-------------------------------------------

If ReturnSize <> 0 Then

If ResultIndex < NumCells Then

If ResultIndex < ReturnSize Then

ResultIndex = ReturnSize

End If

End If

End If


ReDim Preserve ResultArray(1 To ResultIndex)

For N = NumCells + 1 To ReturnSize

ResultArray(N) = vbNullString

Next

----------------------------------------------------------


Regards,

Kuldeep
 
Hi Kuldeep ,


I am not able to understand your problem.


The procedure written by Pearson , does not have any limits as to the first 10 items.


If your input range has 100 unique items , and if you select a range of only 10 cells , and if you array enter the formula into those 10 cells , then only those 10 cells will be populated with the unique distinct values.


If you select a range of 50 cells , and if you array enter the formula into those 50 cells , then only those 50 cells will be populated with the unique distinct values.


If you select a range of 100 cells , and if you array enter the formula into those 100 cells , then all the 100 cells will be populated with the unique distinct values.


Narayan
 
Hi NarayanK,


Sorry for bothering you for my Ignorance. I never knew this trick to enter a array formula. I was just trying to drag downward...


While after dragging the formula you need to make it an array by CTRL+SHIFT+ENTER.


Thanks NarayanK. Problem resolved :)


Regards,
 
You can check the code


Columns("B:B").Select

'Selection.AutoFilter

Range("B:B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("F2" _

), Unique:=True
 
Back
Top