Beginner needs guidance for large data handling

Hi fellow Excel users,

So I have been using Excel quite casually until lately when I have encountered a large project where I need to do some major analyze work, and I am not really sure how I am going to handle it in the best way. I am going to do some analyzes this year, but I would also like to make it as convenient as possible because someone else will need to do it next year, so I need to make an easy solution!


So I'll try to describe my problem.

I have got two data sets:

1. 50-100 Product files with ~10 000 rows each, ~10 columns
containing [Product, Buyer ID, some other variables]


2. 6-7 Sales info files equalling roughly ~6 million rows, ~36 columns. (I have for now copy-pasted everything tab-separated into a .txt file so I can handle it as one unit)
containing [Product, Buyer ID, some other variables]

Each ROW means a sale.


So I need to check each Product file toward the 6 million rows in the sales info files.

So lets say I open Product file 1. Then I want to filter out the Sales info in the product column for that product.

Then, for each Buyer ID, I want to add upp all the rows with the corresponding buyer ID, providing me with a number.



When I have extracted that data I then have to paste it into another document doing my calculations/analyzes, then copy/paste into a word document to present.



So right now this mean quite much manual labor, and I want to try to make it as easy and quick as possible since it is a once-a-year thing, and I wont be the one doing it next year.
Right now there is so many steps with several files, copy-paste, filter etc so it isn't very handy.



I found the powerpivot extension and thought that it would be my savior since I can handle all the 6 million rows in one sheet, but as I've understood the functionality isn't exactly the same as in a regular spreadsheet.


I'm not sure how well described my formulation is so I might as well post this and ask me questions if there's anything you wonder!

It strikes me that Excel is not the most appropriate tool to use, if you have a large table of 6 million rows. Perhaps you should consider using a database, like Access.

Hope this helps.

Pete

Originally Posted by Pete_UK

Pete

Okey, I have never really used Access, I will try to look into some youtube intro videos and see how it works.