Data Challenge: turning massive amounts of sales order data into summarized customer data

The formulas in the attached file should work, but you would need to remove the "Total" lines.
Here is a link with a quick way to delete those rows: http://chandoo.org/wp/2013/03/15/rem...c-value-excel/

The other option would be to add a condition to EVERY formula that ignores the total lines: Data!b$2:b$189,"<>"&"Total"

The "Order of *" formulas were not correct.
The attached file should fix that.

Hi amiller,

Here's a custom routine for a lot of rows

Please Login or Register  to view this content.

The way I would complete the "desired" tab is by copying the list of customers, pasting and removing duplicates. Then copy the new list and past it below 5 times. Then sort, and you will have a group of 6 for each customer. paste the formulas for the first six lines (which should be the first customer), and drag down.

Hi amiller,

I think I can make it even faster - but it should be pretty quick as it is

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.

Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name


Then you need to have your Data-Desired book active!

Hi,

This is as fast as I can make it, but for 640K rows, you might need the speed

Please Login or Register  to view this content.

I made some changes to my original file, so here are the steps to follow:
1. Paste the unique "Account #s" in N3 (The formulas are set up for at most 30,000 accounts)
2. Highlight cells B2:J10 and drag down

Let me know if the formulas I constructed are working correctly.

I recommend dragging down B2:B10 down, then C2:c10, etc.... copy and pasting values after each column is complete, and saving EVERY time. These formulas are referencing over 600000 rows, and having a large number of them will slow down excel quite a bit, to the point of possibly crashing.

The solution to get lists for columns A and B looks pretty easy to do. Use these formulae in column C2 to C8 copy across:

=SUMIF(Data!$A:$A,$A2,Data!C:C)
=COUNTIFS(Data!$A:$A,$A2,Data!C:C,">0")
=IFERROR(AVERAGEIFS(Data!C:C,Data!$A:$A,$A2),0)
=COUNTIFS(Data!$A:$A,$A2,Data!C:C,"<=99")
=COUNTIFS(Data!$A:$A,$A2,Data!C:C,"<=199")
=COUNTIFS(Data!$A:$A,$A2,Data!C:C,"<=499")
=COUNTIFS(Data!$A:$A,$A2,Data!C:C,">=500")

Then just select C2:H8 and copy then paste beside each account group.

Hi amiller,

Here's the new version fully tweaked

Please Login or Register  to view this content.

Duplicate Post

That's great. It definitely will be quite slow. Drag one column down, copy-paste values, save, and then drag the next one down. Limiting the numbers of formulas that are active is the key.

Hi amiller,

That's the trouble with formulas all over the spreadsheet - when you open the sheet, it has to do all those calculations - I think that there is a definite time advantage in the VBA routine I wrote for you??????

You would probably be better off if you used a database program for this instead of Excel. Unless you have a very fast computer, this will be like driving spikes with a small tack hammer.

Hi amiller,

Yeah - I think it could run in less than 5 seconds, please let me know how long it actually takes - this code is more like a 28 oz maul than a little tack hammer

Great! You're welcome! Glad to hear that my timing estimate was reasonable

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Here's what I got: EForum Example2.xlsx

You're welcome!

xladept - if you're up for another challenge, I've got a couple more wrinkles to add:

1) change the order size criteria to the following:
Orders </= $0
Orders of $0-$25 (greater than $0 and less than $25)
Orders of $25-$50 (greater or equal to $25 and less than $50)
Orders of $50-$99 (greater or equal to $50 and less than $99)
Orders of $100-$200 (greater or equal to $100 and less than $200)
Orders of $200-$500 (greater or equal to $200 and less than $500)
Orders >/= $500

2) the more challenging request - in addition to pulling the count of orders in each size criteria, also report the sum of those orders (i.e. if there are 3 orders over $500: <$1000, $1,500, $1,300>; a value of $3,800 would be reported, ideally in the column just to the right of the count for each)

If wrinkle #2 is very cumbersome to include as part of the initial script, it could be written as a separate script and I could combine the data. I was hoping to find a "countif" that I could replace with "sumif" in your original script but obviously that's not how these things work.

amiller

Hi,

I'll take a look tomorrow

Hi AMiller,

Try this:

Please Login or Register  to view this content.

Hi Andy,

You're welcome!

BTW - Let me know if the course actually addresses these kind of issues.