Calculations on Large list of data with Duplicates

[apc1968]

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:

[TABLE="width: 217"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Policy Type[/TD]
[TD]Earnings[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Johnson[/TD]
[TD]Home[/TD]
[TD]£60[/TD]
[/TR]
[TR]
[TD]Kennedy[/TD]
[TD]Home[/TD]
[TD]£55[/TD]
[/TR]
[TR]
[TD]Jones[/TD]
[TD]Motor[/TD]
[TD]£45[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]Motor[/TD]
[TD]£89[/TD]
[/TR]
[TR]
[TD]Jones[/TD]
[TD]Travel[/TD]
[TD]£70[/TD]
[/TR]
[TR]
[TD]Kennedy[/TD]
[TD]Travel[/TD]
[TD]£60[/TD]
[/TR]
[TR]
[TD]Williams[/TD]
[TD]Home[/TD]
[TD]£22[/TD]
[/TR]
[TR]
[TD]Williams[/TD]
[TD]Motor[/TD]
[TD]£42[/TD]
[/TR]
[TR]
[TD]Jones[/TD]
[TD]Motorcycle[/TD]
[TD]£55[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[mikerickson]

What formulas cause the freezing?

To count how many customers have three policies,
=SUMPRODUCT(--(COUNTIF(A2:A40000,A2:A40000)=3))/3


To find out how many customers have both a Home and a Motor policy, can we assume that no one has two Home (or two Motor) policies?

 

[BengalMagic]

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:

[TABLE="width: 217"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Policy Type
[/TD]
[TD]Earnings
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Johnson
[/TD]
[TD]Home
[/TD]
[TD]£60
[/TD]
[/TR]
[TR]
[TD]Kennedy
[/TD]
[TD]Home
[/TD]
[TD]£55
[/TD]
[/TR]
[TR]
[TD]Jones
[/TD]
[TD]Motor
[/TD]
[TD]£45
[/TD]
[/TR]
[TR]
[TD]Smith
[/TD]
[TD]Motor
[/TD]
[TD]£89
[/TD]
[/TR]
[TR]
[TD]Jones
[/TD]
[TD]Travel
[/TD]
[TD]£70
[/TD]
[/TR]
[TR]
[TD]Kennedy
[/TD]
[TD]Travel
[/TD]
[TD]£60
[/TD]
[/TR]
[TR]
[TD]Williams
[/TD]
[TD]Home
[/TD]
[TD]£22
[/TD]
[/TR]
[TR]
[TD]Williams
[/TD]
[TD]Motor
[/TD]
[TD]£42
[/TD]
[/TR]
[TR]
[TD]Jones
[/TD]
[TD]Motorcycle
[/TD]
[TD]£55
[/TD]
[/TR]
</tbody>[/TABLE]


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

It sounds as though you need to be linking your spreadsheet to a table in an Access database.

It's very simple and painless and then you'll be able to query your data until your heart's content without any freezes

 

[apc1968]

What formulas cause the freezing?

To count how many customers have three policies,
=SUMPRODUCT(--(COUNTIF(A2:A40000,A2:A40000)=3))/3


To find out how many customers have both a Home and a Motor policy, can we assume that no one has two Home (or two Motor) policies?

Hi - thanks for very quick reply! - I was originally trying to use Countif on each row to show how many times each name is repeated and then analysing the number of products into columns with Policy Type for the column header - slow and caused machine to crash

On the second point - some customers may have 3 Home Policies and 4 Motor for example

Really appreciate the response - thanks

A

 

[apc1968]

Hi - I get that the formula should work - but it returns a '0' - sorry, any ideas?

Thanks

 

[mikerickson]

Avoid using whole column references.
Did the formula given work with your large data base for "how many customers with n policies"?
(note: duplicate policy types will each be counted)

As to how many with Home&Moter, still working...

 

[apc1968]

Hi - tried your formula on a small test example - works perfectly. If I extend it to me 40k records then it just shows a 0 and refuses to calc it....?