Forum Discussion
formula with an if then suffix if duplicate in column above cell
Hi, I'm pretty new with excel but I'm trying to write a formula. starting in D8 i have a drop down with "build", "Modification", and "dismantle". starting in column E8 I have "tag number" and in F8 i have Request number. I want to make it so if i have modification selected the Request number will fill in based on Tag# *2-1 so it will always be an odd number and then i want it to suffix with a -1,-2,-3 and so on so there is no repeat numbers since we will be doing multiple modifications on each tag#. so far i have =IF(D9="build",E9*2-1,)&IF(D9="dismantle",E9*2,)&if(d9="modification",e9*2-1,) but i don't know how to do the suffix. Thanks in advance
3 Replies
Hi adam rebalkin,
Try this
=IF(D9="build",E9*2-1,IF(D9="modification",E9*2,IF(D9="dismandle",E9*2-1)))
For Suffix
=IF(D9="build",E9*2-1&"-1",IF(D9="modification",E9*2&"-2",IF(D9="dismandle",E9*2-1&"-3")))
If this is not you expected, please send sample file with your expected result. I will reply.
Thank you for the reply, we are getting close but not quite. I don't want dismantles or builds suffixed,only modifications. also the modification suffix can't just be just -2, i need it to check the column up above for duplicates and assign the next number
example:
2-1
2-2
4-1
4-2
4-3
I've attached this portion of the spreadsheet with some formulas and how it should look in the areas i can't figure out.
thanks again
I feel like I'm close with :=IFS(D9="dismantle",E9*2,D9="build",E9*2-1,COUNTIF($F$8:F8,F9(D9="modification",E9*2-1&"-"&1)>1),F9+1)
I know its a little drawn out and hard to read.
