Don't count duplicates

[RPowlett]

Hi,

I hope someone can help with this query.

I am looking to record each of my team members activity excluding duplicate results.

Here is some sample data:

Phoner Company Name Date of Meeting DD/MM/YY Month Meeting Held in Initial Meeting Type AA1F or Tel "Does the company have a DB Scheme? Y/N" Opportunity Booked on Total Fee/Bkge Consultants Name Pipeline, Work In Progress, Not Interested, Won or Lost Caller 1 T J Smith & Nephew 01/03/2015 March Telephone No Opportunity 1 Consultant 11 Not Interested Caller 1 Streamline 01/05/2015 May Face To Face Yes Opportunity 1 £10,000 Consultant 20 Won Caller 1 Streamline 03/06/2015 June Telephone No Opportunity 1 Consultant 1 Won Caller 1 Streamline 03/06/2015 June Face To Face Yes Opportunity 2 Consultant 12 Won Caller 1 Street League 01/08/2015 August Telephone No Opportunity 3 Consultant 7 Won Caller 1 T J Smith & Nephew 01/11/2015 November Face To Face Yes Opportunity 2 Consultant 3 Won Caller 1 Streamline 02/11/2015 November Face To Face Yes Opportunity 1 Consultant 4 Won Caller 1 Streamline 02/11/2015 November Telephone Yes Opportunity 2 Consultant 2 Won Caller 1 Streamline 05/12/2015 December Face To Face Yes Opportunity 1 Consultant 8 Won Caller 1 Abbott Laboratories 16/05/2015 May Face To Face Yes Opportunity 1 Consultant 20 Won Caller 1 Aberdeen Asset Management August Face To Face No Opportunity 1 Consultant 6 Work In Progress

I need to find out how many different opportunities each caller has made counting duplicate opportunities for each company only once. I would also need a formula for the total amounts of the won opportunities for each caller.

For example Caller 1 has made a total of 5 opportunities so far worth £10k.

I tried this formula for the number of opportunities but I kept getting an inaccurate number:

{=SUM(IF(FREQUENCY(IF('BDT Meetings Log'!A2:A2000=A3,IF(1-('BDT Meetings Log'!B2:B2000=""),IF(1-('BDT Meetings Log'!G2:G2000=""),MATCH('BDT Meetings Log'!B2:B2000&'BDT Meetings Log'!G2:G2000,'BDT Meetings Log'!B2:B2000&'BDT Meetings Log'!G2:G2000,'BDT Meetings Log'!J2:J2000="Won")))),ROW('BDT Meetings Log'!G2:G2000)-ROW('BDT Year Stats'!A3)+1),1))}

Can someone help me please?

RPowlett

 

[Aladin Akyurek]

Care to repost the sample? Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl
is also a method for posting an exhibit.<strike></strike>

 

[RPowlett]

Hi Aladin,

Thanks for responding. I'm really struggling to post the sample. I'll try to use a smaller sample if this helps?

 

[Aladin Akyurek]

Hi Aladin,

Thanks for responding. I'm really struggling to post the sample. I'll try to use a smaller sample if this helps?

I don't understand why. But a scaled-down would be ok...

 

[RPowlett]

Hi Aladin,

I think it may have something to do with me using a work computer but I'm not sure.

Thanks and please see the smaller sample below:


Phoner Company Name Date of Meeting DD/MM/YY Month Meeting Held in Initial Meeting Type AA1F or Tel "Does the company have a DB Scheme?
Y/N" Opportunity Booked on Total Fee/Bkge Consultants Name Pipeline, Work In Progress, Not Interested, Won or Lost
Caller 2 Tagged Resources 07/01/2015 October Telephone No Opportunity 1 Consultant 19 Not Interested
Caller 1 T J Smith & Nephew 01/03/2015 March Telephone No Opportunity 1 Consultant 11 Not Interested
Caller 1 Streamline 01/05/2015 May Face To Face Yes Opportunity 1 £10,000 Consultant 20 Won
Caller 1 Streamline 03/06/2015 June Telephone No Opportunity 1 Consultant 1 Won
Caller 1 Streamline 03/06/2015 June Face To Face Yes Opportunity 2 Consultant 12 Won
Caller 1 Street League 01/08/2015 August Telephone No Opportunity 3 Consultant 7 Won

In this sample the two formulas should show that caller 1 has 4 opportunities worth a total of £10k so far.

Thank you

RPowlett

 

[Aladin Akyurek]

The following method does not require to download anything:

http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

When you paste the cells you copied from your sheet, place your mouse in the pasted bit, activate the Go Advaced button, then activate the Tables icon, which allow you to put a full grid around the pasted cells.

 

[RPowlett]

Phoner	Company Name	Date of Meeting DD/MM/YY	Month Meeting Held in	Initial Meeting Type AA1F or Tel	Does the company have a DB Scheme? Y/N	Opportunity Booked on	Total Fee/Bkge	Consultants Name	Pipeline, Work In Progress, Not Interested, Won or Lost
Caller 2	Tagged Resources	07/01/2015	October	Telephone	No	Opportunity 1		Consultant 19	Not Interested
Caller 1	T J Smith & Nephew	01/03/2015	March	Telephone	No	Opportunity 1		Consultant 11	Not Interested
Caller 1	Streamline	01/05/2015	May	Face To Face	Yes	Opportunity 1	£10,000	Consultant 20	Won
Caller 1	Streamline	03/06/2015	June	Telephone	No	Opportunity 1		Consultant 1	Won
Caller 1	Streamline	03/06/2015	June	Face To Face	Yes	Opportunity 2		Consultant 12	Won
Caller 1	Street League	01/08/2015	August	Telephone	No	Opportunity 3		Consultant 7	Won
Caller 1	T J Smith & Nephew	01/11/2015	November	Face To Face	Yes	Opportunity 2		Consultant 3	Won
Caller 1	Streamline	02/11/2015	November	Face To Face	Yes	Opportunity 1		Consultant 4	Won
Caller 1	Streamline	02/11/2015	November	Telephone	Yes	Opportunity 2		Consultant 2	Won
Caller 2	Tagged Resources	02/11/2015	November	Face To Face	No	Opportunity 1		Consultant 16	Not Interested
Caller 1	Streamline	05/12/2015	December	Face To Face	Yes	Opportunity 1		Consultant 8	Won

<tbody>
</tbody>

I think I done it thanks Aladin.

In the above example Caller 1 has won 5 unique opportunities for the companies totalling £10k so far.
This was my attempt for the number of won opportunities for each caller:

{=SUM(IF(FREQUENCY(IF('BDT Meetings Log'!A2:A2000=A3,IF(1-('BDT Meetings Log'!B2:B2000=""),IF(1-('BDT Meetings Log'!G2:G2000=""),MATCH('BDT Meetings Log'!B2:B2000&'BDT Meetings Log'!G2:G2000,'BDT Meetings Log'!B2:B2000&'BDT Meetings Log'!G2:G2000,'BDT Meetings Log'!J2:J2000="Won")))),ROW('BDT Meetings Log'!G2:G2000)-ROW('BDT Year Stats'!A3)+1),1))}

Not sure what I did wrong but I'd also need a formula that shows the total amount won by each caller, if possible.

Are you able to help with the two formulas?

Thanks

RPowlett

 

[Aladin Akyurek]

[...]

I think I done it thanks Aladin.

In the above example Caller 1 has won 5 unique opportunities for the companies totalling £10k so far.
This was my attempt for the number of won opportunities for each caller:

{=SUM(IF(FREQUENCY(IF('BDT Meetings Log'!A2:A2000=A3,IF(1-('BDT Meetings Log'!B2:B2000=""),IF(1-('BDT Meetings Log'!G2:G2000=""),MATCH('BDT Meetings Log'!B2:B2000&'BDT Meetings Log'!G2:G2000,'BDT Meetings Log'!B2:B2000&'BDT Meetings Log'!G2:G2000,'BDT Meetings Log'!J2:J2000="Won")))),ROW('BDT Meetings Log'!G2:G2000)-ROW('BDT Year Stats'!A3)+1),1))}

Not sure what I did wrong but I'd also need a formula that shows the total amount won by each caller, if possible.

Are you able to help with the two formulas?

Thanks

RPowlett

Thanks for the sample.

Just looking at the formula you have, we should have:

{=SUM(IF(FREQUENCY(IF('BDT Meetings Log'!A2:A2000=A3,IF(1-('BDT Meetings Log'!B2:B2000=""),IF(1-('BDT Meetings Log'!G2:G2000=""),IF('BDT Meetings Log'!J2:J2000="Won",MATCH('BDT Meetings Log'!B2:B2000&'BDT Meetings Log'!G2:G2000,'BDT Meetings Log'!B2:B2000&'BDT Meetings Log'!G2:G2000,0))))),ROW('BDT Meetings Log'!G2:G2000)-ROW('BDT Meetings Log'!G2)+1),1))}

Does this deliver the goods you are after? Note that you need lock (dollarize) all ranges from BDT, if you are copying this down.

 

[RPowlett]

That's brilliant thanks Aladin

I really appreciate you help with this thank you.

 

[Aladin Akyurek]

That's brilliant thanks Aladin

I really appreciate you help with this thank you.

You are welcome.

If you want to sum the corresponding dollar values, replace the stand-alone 1 of the formula with the range to sum.