Currently Being ModeratedMar 20, 2013 12:27 AM (in response to maltman99)
If I've read your question correctly, this should provide the results you want for question 1:
The yellow filled columns are used to construct a single datum for each transaction in the selected quarter, then to count the number of distinct rep/customer transactions for each rep. These columns may be hidden.
Rep names must be entered in the same order in the auxiliary columns of Table 1 as in the columns of Table 2 ("Summary"). Enter the Quarter information into cell A1 of Table 2. The entry must mathc the format used for the quarter information in Table 1.
Formulas (Table 1)
Columns A - D contains entered data.
E2, and filled down: =IF(C=Summary :: $A$1,B&" "&A,"")
F2, filled right to H2, and down: =IF(AND(LEFT($E2,LEN(F$1))=F$1,COUNTIF($E$2:$E2,"="&$E2)=1),MAX(F$1:F1)+1,"")
A2, and filled right: =MAX(Table 1 :: F)
Counting the number of new customers for each rep depends on how that information is recorded in the 1st period column.
Currently Being ModeratedMar 20, 2013 1:38 AM (in response to Barry)
Thanks for the input Barry -- really appreciate it mate.
It won't quite solve this problem for me but could well help me with some others I'm bound to encounter.
The key problem with doing it this way is the size of the dataset I'm working with -- there would be well in excess of 1000 unique customers and the number of records is in the 10's of thousands.
I need to find something along the lines of pivot table style functionality that summarises according to metrics. I can do all revenue totalling stuff fine using sumifs but the counting part has me baffled. Not sure if you can do some sort of combination using count and frequency or match style functions. Wlll keep digging.
Currently Being ModeratedMar 20, 2013 2:07 AM (in response to maltman99)
You're going to find Numbers quite slow at doing anything involving a table with 'records in the 10s of thousands.'
It's just not designed to handle large tables. You'll find MS Excel, or one of the open source Office applications (OpenOffice.org, LibreOffice, NeoOffice) a better tool for large datasets.