I was in the process of putting together a workbook today, when I realized the inefficient system I was using was creating way too big a workbook. Each sheet had something like 250,000 formulas and I needed hundreds of sheets. Anyways, on to what I need help with...

Basically I have one sheet called "Sales" where most of the data for the week would get inputted. There would be ~1000 sales per week between ~200 unique customers. Each sale has a few columns of very basic information such as customer #, price, discount, etc. Using the raw unsorted data from the Sales sheet, I need to populate a separate sheet for each customer (so 200 customer sheets).

So for example if customer #140 buys 8 products throughout the week, I need a excel to search all 1000 sales on the Sales sheet to find #140's purchases. The result should be 8 neat row of data listed on Customer #140's sheet.

What I was previously using was a huge grid of =IF(A1=140,B1,0)... IF(A1=140,C1,0)... and so on, then dragging it down 1000 rows to populate the sheet with the relevant sales but with lots of empty space. Then using INDEX to collapse the data how I needed it. This huge grid was on every customer sheet, and there were too many formulas.

Are there maybe some smarter ways of using formulas to gather this data together, or am I going to need to resort to using macros for a project like this?

Thanks