Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have been strugling for past several day trying to resolve this issue. Here is what I have and what's needed:

I've got about 20 Excel sheets that contain massive data (around 50K rows/sheet) that need to be filtered. The automatic filter has been applied so I can just pick data. Now, when I choose the data manually, it takes me up to two hours to get what I want. Now imagine if it should be done repeatedly...

I can't use macro for some items are as long as 800! characters (macros can work with 255 chars. per command at max.).

It seems logical to copy the "picking" process within the filter (not the result you get after picking) but I can't figure out how.

Does anybody have any ideas or suggestions, please?

share|improve this question
    
I don't understand what you mean by "picking" - what is your end goal? –  Raystafarian May 20 at 12:50
    
Your requirements are very vague. –  Mike Honey May 20 at 23:46

2 Answers 2

I would suggest a database, and for the picking process I would suggest SQL.

20 tables * 50,000 rows is pretty massive for spreadsheets but it is nothing for a database.

You might use an Express version of MS SQL for this, which is licensed free of cost for both personal and business use and has excellent collaboration functions with Excel.

You could connect your spreadsheets as external tables, you might not going to be able to write to the spreadsheets from SQL but your searches going to be much faster than they are now.

share|improve this answer

You can probably resolve this within Excel, using one or two of the new Add-Ins Microsoft have released.

I would start with Power Query, and use that to consolidate the 20 sheets into one Excel Data Model Table, perhaps with an extra column added so you know where that came from. The Data Model format compresses data so the volume of 1m rows should be OK.

Then I would use Power Pivot to create a Pivot Table, using Slicers and/or Pivot Table filters to meet your "picking" requirements.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.