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

 

[dappy]

=MID(Network_DATA!$A4,SEARCH("TRX-",Network_DATA!$A4)+4,SEARCH("/trx",Network_DATA!$A4)-3)

this is what i have in Col B and gives a rumber, but i'm only getting the result of "Leave". if i change the result of the cell to a value it works?

 

[dappy]

ah ok so if i put this in B it seems to work

=VALUE(MID(Network_DATA!$A2,SEARCH("TRX-",Network_DATA!$A2)+4,SEARCH("/trx",Network_DATA!$A2)-3))

thanks again for your help, that's twice this month you've saved me and understood my ramblings, you're a star

 

[Fluff]

The MID function (along with LEFT & RIGHT among others) always return text, not numbers.

Glad you sorted it & thanks for the feedback.

 

[dappy]

Hi Again,

So, this works most of the time

=IF(J76=4,1,IF(COUNTIFS(A:A,A76,B:B,"<"&B76)<D76,"1","3"))

But if J doesnt equal 4 and B is less than the other value in B that does equal 4 then i need to be 3, and at the moment its not.

any further help much appreciated, again :D

Carl

 

[dappy]

to try and make things clearer,

in A i have multiple copies where some are equal, in B i have a list of values. in J i have either 4 or 0,1,3. if J=4, then result is always 1. if J is anything else then depending on count of B compared with D would result in either a 3 or 1. hope this helps