put value depending on last digit in unorganised list

[dappy]

So i have this

corridor1 2
corridor2 2
corridor3 2
outilane4 2
outilane1 2
outilane3 2
outilane2 2

column A for corridor i have a count of 3 and outilane a count of 4. Column B i'm told that i must delete an amount with a value of what is to be left, in this case
i need to have 2 of corridor and 2 of outilane left.

so i need to put in column C a value of "leave" or "delete" but it depends on the last digit in each name. so for corridor i have 1,2 and 3 and for outilane
i have 1,2,3 and 4. for corridor i need to delete 1 but it must be corridor3. for outilane i have to delete 2 but it
must be 3 and 4, so the last in each. is this possible? or even understandable! its not possible to have the list organised according to last value I'm sorry to say.

Much thanks in advance

Carl

 

[footoo]

=IF(RIGHT(A2,1)+0>B2,"delete","leave")

 

[dappy]

sorry, my description is junk. I'll start again

the character at the right of corridor and outilane could be anything. column B has the counts but unordered. Column c shows how many must be left. in this case corridor998 3 is deleted and outilane 4 and 3 are deleted.

corridor998 1 2
corridor998 2 2
corridor998 3 2
outilane231 3 2
outilane231 4 2
outilane231 2 2
outilane231 1 2

Hopefully that's clearer. apologies for the confusion

 

[Fluff]

How about
=IF(B2>C2,"Delete","Leave")

 

[dappy]

no, its never going to be that easy
so it could be like this below.
I guess we would have to count each selection then we'd know how many to delete and then choose the highests value in colB?

corridor998 7 2
corridor998 8 2
corridor998 6 2
outilane231 5 2
outilane231 6 2
outilane231 4 2
outilane231 3 2

 

[Fluff]

How do you expect to get a working solution if you keep posting completely bogus data?

Please use the XL2BB add-in to post some accurate data along with the expected results.

 

[dappy]

yeah really sorry about that, glad my life isnt in as much a state as my descriptions.

hope this makes more sense to you than me!

Book25
	A	B	C	D
1	cam occurance	id ID	REQ	Leave/deleter
2	corridor-198/camera-198	7	1
3	corridor-198/camera-198	8	1
4	corridor-200/camera-200	9	1
5	corridor-200/camera-200	8	1
6	corridor-202/camera-202	5	2
7	corridor-202/camera-202	6	2
8	corridor-202/camera-202	4	2
9	corridor-202/camera-202	3	2
Sheet1

 

[Fluff]

Ok, how about
=IF(COUNTIFS(A:A,A2,B:B,"<"&B2)<C2,"Leave","Delete")

 

[dappy]

that is superb! thank you so much again!

although can i add just one caviat?

that works with values but not with cells that have formulas in. is there a =value of or something i can use?

 

[Fluff]

It does work for cells with formulae, as long as the formula is returning a number & not text.