Counting duplicates and assigning a tag

[Msdtitus]

I am looking for a formula which can assign a code based on predefined criteria

	A	B	C	D	E
1	Strategy	Name	Area	Calls	Priority
2	S1	Amy	Bakery	0
3	S2	Jane	Bakery	1
4	S3	Jane	Bakery	1
5	S1	Amy	Bakery	0
6	S1	Peter	Bakery	2
7	S3	Peter	Bakery	2
8	S2	Jane	Bakery	1
9	S2	Amy	Bakery	0
10	S1	Jane	Bakery	1

<tbody>
</tbody>

Part 1
The formula must look at how many calls was assessed for each person

What I need is a formula that looks at only the first 2 entries of a person(Amy) which should be given priority 1, and the balance to be given the priority 3 tag

Because Jane has 1 call already, only 1 call must be given the P2 code and the rest get a P3 code

in the event that a person has 2 or more calls, then they will automatically receive a P3 code

so ideally it should look like this when the code runs

	A	B	C	D	E
1	Strategy	Name	Area	Calls	Priority
2	S1	Amy	Bakery	0	P1
3	S3	Jane	Bakery	1	P2
4	S3	Jane	Bakery	1	P3
5	S3	Amy	Bakery	0	P3
6	S1	Peter	Bakery	5	P3
7	S3	Jake	Bakery	2	P3
8	S2	Jane	Bakery	1	P3
9	S2	Amy	Bakery	0	P1
10	S3	Jane	Bakery	1	P3

<tbody>
</tbody>

Part 2
The above formula should also assign these codes based on the preferred strategy

As illustrated above, Amy has calls with the S1,S2 and S3 codes respectively and the Formula will look for the first look for a qualifying S1 it can find and assign them the P1 code, then S2 and if none exist, S3

For Jane, the best Strategy is S2 so that will receive the P2 code and the rest will receive P3 codes

I'm not sure if this is clear enough but this is a really complex piece of work.

 

[Msdtitus]

Good Morning

is anyone able to assist me with the above request?

 

[Msdtitus]

Good Morning Guys

I would really like some assistance on this as I am trying all sorts of formulas but none seems to be working

I tried the following formula but this seems to be giving me half of what I need in that it flags the 1st instance of the duplicate but everything else gets the same Flag. See the formula below

=IF(COUNTIF($A$2:A2,A2)>2,"P3",IF(COUNTIF($A$2:A2,A2)=1,"P1","P2"))

Any assistance will be really valued

many thanks
Masood

 

[Msdtitus]

Good Morning all

i have still not been able to find a solution so if there is anyone on the Forum that may be able to assist i will geatly appreciate it