Forum Discussion
In a column of Data, Find 2nd, 3rd, nth occurrences and apply a value in the corresponding column
Hi all,
Attached is a spreadsheet that gives an example of what I'm working with.
In it, I'm trying to create a formula that searches all of column F for the exact same tag numbers. And if it finds one it will state "Multiple Times" in Column I (Notes).
And if the same tag is found a second time, then it will apply a value to the second, third (and so on) occurrence as "Attached" in Column H under Label.
The formulas I was working with in the past looked like:
=VLOOKUP(OFFSET(F1,0,100,1),F2:F2000,8,FALSE)
or using IF & MATCH instead of VLOOKUP.
They both provided similar results but I'm still stumped on it.
The goal I'm trying to reach is that when I run my Macro, I want it to find these duplicates and apply the "Multiple Times" in the Notes column to that corresponding animal with that tag.
And then I want it to recognize that there are 2nd(later) times where it occurs again and to let me and my employees see this so we can take care of the animal right there instead of later.
Anyone got any ideas?
- Place this formula in cell I2 and copy down:
=IF(COUNTIF(F$1:F1,F2)>0,"Again","")- This is great!
But how can I apply it in Macro/VBA?
It seems to be failing.
My friend stated I may need to do R1C1??The VBA syntax to add this to cell I2:
Range("I2").Formula = "=IF(COUNTIF(F$1:F1,F2)>0,""Again"","""")"Note the doubled-up quotes.
