In a column of Data, Find 2nd, 3rd, nth occurrences and apply a value in the corresponding column

[RobG92]

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?

[#2]

Place this formula in cell I2 and copy down:
=IF(COUNTIF(F$1:F1,F2)>0,"Again","")

[#3]

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??

[#4]

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.

[#5]

I'm not an expert by any means in Excel Macros/VBA. We just had an existing Macro and I was hoping to manipulate it to do this for us.
We need it to copy and paste in the column but what you provided isn't working...not sure If I'm doing it right or not

[#6]

Best if you explain what it is needs doing, what my code does which is undesirable and also show what code you already have and where it needs adjusting.

[#7]

So in the original Experiment attached to the original post.
We have this formula:

Application.CutCopyMode = False
Range("K1").Value = "Note"
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(C[-2],RC[-2])>1,""Multiple times"","""")"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K2000")

The person who originally made it up, I guess, decided to use R1C1 reference style.
So I guess what I'm asking is how do I apply your formula to regular reference style or convert your formula to R1C1.

Also whats happening when I apply your formula is that its giving me an error when running the Macro. application defined or object defined error.
The idea behind the formula is that it should be able to apply to an entire column of active data (it will stretch between hundreds of rows) find the duplicates, apply the "Again" or "Multiple Times" to that specific item in the row under the Notes Column, column I.

The R1C1 puts itself in column K and applies a formula that reaches over to apply the formulas. in another column.

But We are looking to enhance the formula, essentially, and instead of marking them ALL multiple times...we want to mark singular ones, as the second or third occurrence etc...

If that makes sense.

[#8]

Here is a formula that is close to what I mean: But it keeps applying it to all of the cells and not the adjacent cell in the same row:

"=IF(COUNTIF(R1C[-3]:R[-1]C[-3],RC[-3])>0,"Repeat Tim","")"

[#9]

If you have devised a formula that works (manually, in Excel) the simple way to get the syntax of that formula for VBA is:
- Select cell with proper formula
- Hit Alt+F11 (Opens the VBA editor)
- Hit control+g (opens the immediate pane)
- Type:
?ActiveCell.FormulaR1C1
- place cursor on that line and hit Enter.
- Any quotes which are within the formula must be doubled-up when writing in VBA.
You can slo use ActiveCell.Formula (so without the R1C1), but the advantage of the R1C1 is that the VBA placing formula will become independent of the location of the formula. The cell addresses with numbers between square brackets in the formula become relative to where you place the formula. Clear as mud?