"", MATCH(User_Name_Extract,User_Name_Extract&"",0)), ROW(User_Name_Extract)-ROW('Data Source'!\$A\$2)+1),ROW(User_Name_Extract)-ROW('Data Source'!\$A\$2)+1),ROWS(B\$13:B13)))) \$C\$13 contains a unique count formula. B\$13:B13 is the position on the list (so if there were only 10 unique names you wanted to extract, you could get away with dragging it to B\$13:B23). "User_Name_Extract" is the range, which in my case is 32000 rows xD You can probably get a clearer understanding from the video than I can give. Is there any solution when applying this to such large datasets? And what particularly is causing the trouble? Any help would be grand .">
• Hot Topics

# Excel Extracting unique data from Large Datasets - Excel Magic Trick 698

Hey I couldn't find an answer for this so thought I'd sign up! As you can guess, i've been trying out the method in this video: https://www.youtube.com/watch?v=IhuURsu0jdI A series of formulas for extracting unique data, compiling it into a list, and then using this list to make a dynamicly updating DV List. It works just great. The trouble is, when it is applied to large data sets, it seriously ties up the cpu. My question is how can I reduce or eliminate this problem? What elements can be altered or removed to improve performance? The below formula extracts a list of unique names in sequence. From there it's fairly simple to make the DV List. =IF(ROWS(B\$13:B13)>\$C\$13,"",INDEX(User_Name_Extract, SMALL(IF(FREQUENCY(IF(User_Name_Extract<>"", MATCH(User_Name_Extract,User_Name_Extract&"",0)), ROW(User_Name_Extract)-ROW('Data Source'!\$A\$2)+1),ROW(User_Name_Extract)-ROW('Data Source'!\$A\$2)+1),ROWS(B\$13:B13)))) \$C\$13 contains a unique count formula. B\$13:B13 is the position on the list (so if there were only 10 unique names you wanted to extract, you could get away with dragging it to B\$13:B23). "User_Name_Extract" is the range, which in my case is 32000 rows xD You can probably get a clearer understanding from the video than I can give. Is there any solution when applying this to such large datasets? And what particularly is causing the trouble? Any help would be grand