0

I am having an excel issue that is driving me crazy, mostly because I feel like I have reached some limit of excel where I do not have the processing power to do this task.

I have two columns that I need to do the following:

one column contains record ids and the other column contains information about those ids. The total number of record id's is 185629 and the total number of records containing the information is 498776.

My goal is to compare the two columns and do a special vlookup that will return the information values and if it finds an id that is the same it will return it in the same record but with just a comma or some other delimiter to separate the information. The link I sent does a better job of explaining my task.

https://www.extendoffice.com/documents/excel/3120-excel-return-multiple-values-separated-by-comma.html

I have followed both methods, the first method works but it takes about 1 second to run per record, so with my calculations it would take 52 or so hours to complete the task. The other way, using the kutools plugin just sends the computer into limbo where it says not responding until the computer falls asleep as per sleep settings. I feel like this method will either not work at all or still take 52 hours as well.

I have also followed all the tips in this guide to make formulas run faster:

http://professor-excel.com/15-ways-to-speed-up-excel/

My processor is a laptop intel core i7-4720HQ 2.59GHZ and I have excel set to utilize all 8 cores.

My spreadsheet is originally from an SQL database which I imported though the data tab on excel. What do you guys think? Is this too much data for a custom vlookup to crunch quickly? Is there anything else I should try? Have you worked with this much data in Excel Before? Thanks!

Kevin
  • 391
  • 3
  • 6
  • 22
  • If you have such a large dataset, may we ask why you switched from SQL to Excel in the first place? I think a SQL database would have no trouble handling your data set. – Tim Biegeleisen Jun 26 '17 at 03:24
  • Yes I was afraid you would say that. The reason is is that I am having trouble writing a query that will display the information in the format that I would like it in. If I must I will delve back into SQL do you think I have hit the "limits" of Excel? Thanks for the response! – Kevin Jun 26 '17 at 03:42
  • Someone here can probably give you the query you need. If you can rephrase your question showing sample input and the desired output, that would be helpful. – Tim Biegeleisen Jun 26 '17 at 03:43
  • I'll try that Thanks! – Kevin Jun 26 '17 at 03:44
  • This is not super simple with SQL but take a look at this: https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 – donCalculator Jun 26 '17 at 04:45
  • Thanks! I'm currently fiddling with a query that I am creating, and I will take the thread you posted into account. – Kevin Jun 26 '17 at 04:53
  • The ID's are sorted in ascending order and I am looping though them according to the ranges they occupy. I tried making them a named range but it just game me a #NAME error. This is odd because in a normal vlookup I can use named ranges no problem. Remember, that I am using the function called SingleCellExtract that is mentioned in my first link. Its a custom function that should return matches into a single cell that is comma delimited. Thanks for the reply! – Kevin Jun 26 '17 at 08:36
  • Thanks I'll give it a try – Kevin Jun 26 '17 at 09:07
  • Yeah looks like its not working correctly on my data thanks for the help though. I'm gonna try to do it in SQL. Nothing 12 hours of extreme focus can't fix haha wish me luck! – Kevin Jun 27 '17 at 02:53

0 Answers0