Hi - new to this but thanks to all for help (Excel for Mac 2011). Have searched and can find lots to help but the size of my data means am crashing by usual methods of sorting - I need a formula for this rather than pivot tables.
I have a list of c. 40,000 rows. Col A is client name (largely different but some duplicates); Col B is Policy type (eg Home, Motor, Travel) and Col C is earnings on that policy (eg £60). An example would be:
<colgroup><col span="2"><col></colgroup><tbody>
</tbody>
So I need to figure out (via a formula) two things:
1) How to count duplicate names (eg appearing 3x = 1 customer; appearing 2x = 2 customers; appearing 1x = 1 customer)
2) How to build a simple table to show the relationship between policies. This would have Motor, Home, Travel, Motorcycle as columns and also as rows - the data would then be how many policies and in each cross point?
I have tried just working the data using Countifs etc, but as soon as I apply it to the whole data set the machine freezes, presumably due to its size
Really appreciate anyones help here - thank you!
A
I have a list of c. 40,000 rows. Col A is client name (largely different but some duplicates); Col B is Policy type (eg Home, Motor, Travel) and Col C is earnings on that policy (eg £60). An example would be:
Name | Policy Type | Earnings |
Johnson | Home | £60 |
Kennedy | Home | £55 |
Jones | Motor | £45 |
Smith | Motor | £89 |
Jones | Travel | £70 |
Kennedy | Travel | £60 |
Williams | Home | £22 |
Williams | Motor | £42 |
Jones | Motorcycle | £55 |
<colgroup><col span="2"><col></colgroup><tbody>
</tbody>
So I need to figure out (via a formula) two things:
1) How to count duplicate names (eg appearing 3x = 1 customer; appearing 2x = 2 customers; appearing 1x = 1 customer)
2) How to build a simple table to show the relationship between policies. This would have Motor, Home, Travel, Motorcycle as columns and also as rows - the data would then be how many policies and in each cross point?
I have tried just working the data using Countifs etc, but as soon as I apply it to the whole data set the machine freezes, presumably due to its size
Really appreciate anyones help here - thank you!
A