How to make Countif with a Dynamic Range

Luchano

Board Regular
Joined
Jul 25, 2013
Messages
84
Hi all!

i'm using the formula below to find duplicates; however, i want to make the formula dynamic, so i don't have to change it everytime new data is added to sheet. Any help will be greatly appretiated.


=IF(COUNTIF(F2:F1774,F2)>1,"Remove",0)


Thank you!!!
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Luchano

Board Regular
Joined
Jul 25, 2013
Messages
84
Code:
[COLOR=#333333]=IF(COUNTIF(F:F,F2)>1,"Remove",0)[/COLOR]

Thank you, but that's not quite what i'm looking for. When you refer to an entire column it creates errors over large sums of data, and it assignes "Remove" to rows that shouldn't be removed.
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
I've never had that issue before...unless you have data beneath your data set...

If you don't want to use the entire column, your only option for a dynamic formula is to input it using VBA.
 

Luchano

Board Regular
Joined
Jul 25, 2013
Messages
84

ADVERTISEMENT

I've never had that issue before...unless you have data beneath your data set...

If you don't want to use the entire column, your only option for a dynamic formula is to input it using VBA.


Ok, so what would be the code?


Thanks for your quick response!!!
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
Sub dynamicRange()

    Range(Cells(2, 7), Cells(Cells(Rows.Count, "F").End(xlUp).Row, 7)).FormulaR1C1 = _
        "=IF(COUNTIF(R2C6:R" & Cells(Rows.Count, "F").End(xlUp).Row & "C6, RC6) > 1, ""Remove"", 0)"


End Sub

Change the two instances of 7 for the column where the formula will go. 7 assumes column G.

8 = H
9 = I
10 = J

etc...
 

Luchano

Board Regular
Joined
Jul 25, 2013
Messages
84
Code:
Sub dynamicRange()

    Range(Cells(2, 7), Cells(Cells(Rows.Count, "F").End(xlUp).Row, 7)).FormulaR1C1 = _
        "=IF(COUNTIF(R2C6:R" & Cells(Rows.Count, "F").End(xlUp).Row & "C6, RC6) > 1, ""Remove"", 0)"


End Sub

Change the two instances of 7 for the column where the formula will go. 7 assumes column G.

8 = H
9 = I
10 = J

etc...

Thank a lot NeonRedSharpie!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,655
Messages
5,549,259
Members
410,905
Latest member
Extjel
Top