Formula to use in Macro
 

I'm trying to set up a macro to do things I normally do by hand, over 15 or
so tabs in a workbook on a monthly basis.

On each sheet, I sort the sheet according to three columns. Then I insert a
column at B. The formula I type by hand is
=IF(COUNTIF(A2:A30,A2)1,"Duplicate",""). Then I double click to fill down,
copy, paste values, and sort descending by that new column. Except that the
A30 changes each month!

I need to set up the macro so that I can reuse it each month on a new
workbook. Everything is the same, except the location of the last row of
information. I normally use
Selection.AutoFill Destination:=Range("B2:B" &
ActiveSheet.UsedRange.Rows.Count)
and that will auto fill to the last row of used data. But I don't know how
to incorporate that into the sort and the end of the range in the if countif
formula.
It looks like this in my macro:
Range("A1:K30").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range( _
"G2"), Order2:=xlAscending, Key3:=Range("F2"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(RC[-1]:R[28]C[-1],RC[-1])1,""Duplicate"","""")"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B30")
Range("B2:B30").Select

Any help or tips for these two things would be most appreciated!!!!

Formula to use in Macro
 

For the COUNTIF part,

change: =IF(COUNTIF(A2:A30,A2)1,"Duplicate","")
to: =IF(COUNTIF(A:A,A2)1,"Duplicate","")

The code for this could then simply be:

ActiveCell.Formula = _
"=IF(COUNTIF(A:A,A" & ActiveCell.Row & ")1,""Duplicate"","""")"

But see a better approach below in the amended code.

The AutoFill part could be:

Range("B2").AutoFill Destination:=Range("B2:B" &
Range("A65536").End(xlUp).Row)

The whole thing turns into:

Range("A:K").Sort _
Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("G2"), Order2:=xlAscending, _
Key3:=Range("F2"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Columns("B:B").Insert Shift:=xlToRight
Range("B2").Formula = _
"=IF(COUNTIF(A:A,A2)1,""Duplicate"","""")"
Range("B2").AutoFill Destination:=Range("B2:B" &
Range("A65536").End(xlUp).Row)

Regards

Trevor


"o1darcie1o" wrote in message
...
I'm trying to set up a macro to do things I normally do by hand, over 15
or
so tabs in a workbook on a monthly basis.

On each sheet, I sort the sheet according to three columns. Then I insert
a
column at B. The formula I type by hand is
=IF(COUNTIF(A2:A30,A2)1,"Duplicate",""). Then I double click to fill
down,
copy, paste values, and sort descending by that new column. Except that
the
A30 changes each month!

I need to set up the macro so that I can reuse it each month on a new
workbook. Everything is the same, except the location of the last row of
information. I normally use
Selection.AutoFill Destination:=Range("B2:B" &
ActiveSheet.UsedRange.Rows.Count)
and that will auto fill to the last row of used data. But I don't know how
to incorporate that into the sort and the end of the range in the if
countif
formula.
It looks like this in my macro:
Range("A1:K30").Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range( _
"G2"), Order2:=xlAscending, Key3:=Range("F2"), Order3:=xlAscending,
_
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(RC[-1]:R[28]C[-1],RC[-1])1,""Duplicate"","""")"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B30")
Range("B2:B30").Select

Any help or tips for these two things would be most appreciated!!!!

Formula to use in Macro
 

Trevor - THANK YOU SO MUCH!!! That does exactly what I needed it to do.

"Trevor Shuttleworth" wrote:

For the COUNTIF part,

change: =IF(COUNTIF(A2:A30,A2)1,"Duplicate","")
to: =IF(COUNTIF(A:A,A2)1,"Duplicate","")

The code for this could then simply be:

ActiveCell.Formula = _
"=IF(COUNTIF(A:A,A" & ActiveCell.Row & ")1,""Duplicate"","""")"

But see a better approach below in the amended code.

The AutoFill part could be:

Range("B2").AutoFill Destination:=Range("B2:B" &
Range("A65536").End(xlUp).Row)

The whole thing turns into:

Range("A:K").Sort _
Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("G2"), Order2:=xlAscending, _
Key3:=Range("F2"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Columns("B:B").Insert Shift:=xlToRight
Range("B2").Formula = _
"=IF(COUNTIF(A:A,A2)1,""Duplicate"","""")"
Range("B2").AutoFill Destination:=Range("B2:B" &
Range("A65536").End(xlUp).Row)

Regards

Trevor


"o1darcie1o" wrote in message
...
I'm trying to set up a macro to do things I normally do by hand, over 15
or
so tabs in a workbook on a monthly basis.

On each sheet, I sort the sheet according to three columns. Then I insert
a
column at B. The formula I type by hand is
=IF(COUNTIF(A2:A30,A2)1,"Duplicate",""). Then I double click to fill
down,
copy, paste values, and sort descending by that new column. Except that
the
A30 changes each month!

I need to set up the macro so that I can reuse it each month on a new
workbook. Everything is the same, except the location of the last row of
information. I normally use
Selection.AutoFill Destination:=Range("B2:B" &
ActiveSheet.UsedRange.Rows.Count)
and that will auto fill to the last row of used data. But I don't know how
to incorporate that into the sort and the end of the range in the if
countif
formula.
It looks like this in my macro:
Range("A1:K30").Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range( _
"G2"), Order2:=xlAscending, Key3:=Range("F2"), Order3:=xlAscending,
_
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(RC[-1]:R[28]C[-1],RC[-1])1,""Duplicate"","""")"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B30")
Range("B2:B30").Select

Any help or tips for these two things would be most appreciated!!!!

Formula to use in Macro
 

You're welcome, glad it worked for you. Thanks for the feedback.


"o1darcie1o" wrote in message
...
Trevor - THANK YOU SO MUCH!!! That does exactly what I needed it to do.

"Trevor Shuttleworth" wrote:

For the COUNTIF part,

change: =IF(COUNTIF(A2:A30,A2)1,"Duplicate","")
to: =IF(COUNTIF(A:A,A2)1,"Duplicate","")

The code for this could then simply be:

ActiveCell.Formula = _
"=IF(COUNTIF(A:A,A" & ActiveCell.Row & ")1,""Duplicate"","""")"

But see a better approach below in the amended code.

The AutoFill part could be:

Range("B2").AutoFill Destination:=Range("B2:B" &
Range("A65536").End(xlUp).Row)

The whole thing turns into:

Range("A:K").Sort _
Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("G2"), Order2:=xlAscending, _
Key3:=Range("F2"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Columns("B:B").Insert Shift:=xlToRight
Range("B2").Formula = _
"=IF(COUNTIF(A:A,A2)1,""Duplicate"","""")"
Range("B2").AutoFill Destination:=Range("B2:B" &
Range("A65536").End(xlUp).Row)

Regards

Trevor


"o1darcie1o" wrote in message
...
I'm trying to set up a macro to do things I normally do by hand, over
15
or
so tabs in a workbook on a monthly basis.

On each sheet, I sort the sheet according to three columns. Then I
insert
a
column at B. The formula I type by hand is
=IF(COUNTIF(A2:A30,A2)1,"Duplicate",""). Then I double click to fill
down,
copy, paste values, and sort descending by that new column. Except that
the
A30 changes each month!

I need to set up the macro so that I can reuse it each month on a new
workbook. Everything is the same, except the location of the last row
of
information. I normally use
Selection.AutoFill Destination:=Range("B2:B" &
ActiveSheet.UsedRange.Rows.Count)
and that will auto fill to the last row of used data. But I don't know
how
to incorporate that into the sort and the end of the range in the if
countif
formula.
It looks like this in my macro:
Range("A1:K30").Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range( _
"G2"), Order2:=xlAscending, Key3:=Range("F2"),
Order3:=xlAscending,
_
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=
_
xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(RC[-1]:R[28]C[-1],RC[-1])1,""Duplicate"","""")"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B30")
Range("B2:B30").Select

Any help or tips for these two things would be most appreciated!!!!

Formula to use in Macro
 

I'm not sure if this will show or not, being that the original post is so
old, but I'll give it a shot...
I'm running through the formula/macro again, and it's putting "Duplicate"
next to every item that's duplicated on the sheet, instead of leaving one
item blank.
Any idea why that's happening, and/or how to correct it? I don't remember it
doing this the first time through (last month).
Thanks in advance....

"Trevor Shuttleworth" wrote:

You're welcome, glad it worked for you. Thanks for the feedback.


"o1darcie1o" wrote in message
...
Trevor - THANK YOU SO MUCH!!! That does exactly what I needed it to do.

"Trevor Shuttleworth" wrote:

For the COUNTIF part,

change: =IF(COUNTIF(A2:A30,A2)1,"Duplicate","")
to: =IF(COUNTIF(A:A,A2)1,"Duplicate","")

The code for this could then simply be:

ActiveCell.Formula = _
"=IF(COUNTIF(A:A,A" & ActiveCell.Row & ")1,""Duplicate"","""")"

But see a better approach below in the amended code.

The AutoFill part could be:

Range("B2").AutoFill Destination:=Range("B2:B" &
Range("A65536").End(xlUp).Row)

The whole thing turns into:

Range("A:K").Sort _
Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("G2"), Order2:=xlAscending, _
Key3:=Range("F2"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Columns("B:B").Insert Shift:=xlToRight
Range("B2").Formula = _
"=IF(COUNTIF(A:A,A2)1,""Duplicate"","""")"
Range("B2").AutoFill Destination:=Range("B2:B" &
Range("A65536").End(xlUp).Row)

Regards

Trevor


"o1darcie1o" wrote in message
...
I'm trying to set up a macro to do things I normally do by hand, over
15
or
so tabs in a workbook on a monthly basis.

On each sheet, I sort the sheet according to three columns. Then I
insert
a
column at B. The formula I type by hand is
=IF(COUNTIF(A2:A30,A2)1,"Duplicate",""). Then I double click to fill
down,
copy, paste values, and sort descending by that new column. Except that
the
A30 changes each month!

I need to set up the macro so that I can reuse it each month on a new
workbook. Everything is the same, except the location of the last row
of
information. I normally use
Selection.AutoFill Destination:=Range("B2:B" &
ActiveSheet.UsedRange.Rows.Count)
and that will auto fill to the last row of used data. But I don't know
how
to incorporate that into the sort and the end of the range in the if
countif
formula.
It looks like this in my macro:
Range("A1:K30").Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range( _
"G2"), Order2:=xlAscending, Key3:=Range("F2"),
Order3:=xlAscending,
_
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=
_
xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(RC[-1]:R[28]C[-1],RC[-1])1,""Duplicate"","""")"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B30")
Range("B2:B30").Select

Any help or tips for these two things would be most appreciated!!!!

Formula to use in Macro
 

That would always have been the case. The formula is counting the number of
occurrences of the value in the cell. If it's greater than one it will
indicate one or more duplicates. But they are all duplicates ... which
one(s) would you highlight ?

But, you could try changing the formula to:

Range("B2").Formula = _
"=IF(AND(COUNTIF(A:A,A2)1,A2=A1),""Duplicate"","" "")"

That should highlight the second and subsequent occurrence but not the
first.

Regards

Trevor


"o1darcie1o" wrote in message
...
I'm not sure if this will show or not, being that the original post is so
old, but I'll give it a shot...
I'm running through the formula/macro again, and it's putting "Duplicate"
next to every item that's duplicated on the sheet, instead of leaving one
item blank.
Any idea why that's happening, and/or how to correct it? I don't remember
it
doing this the first time through (last month).
Thanks in advance....

"Trevor Shuttleworth" wrote:

You're welcome, glad it worked for you. Thanks for the feedback.


"o1darcie1o" wrote in message
...
Trevor - THANK YOU SO MUCH!!! That does exactly what I needed it to
do.

"Trevor Shuttleworth" wrote:

For the COUNTIF part,

change: =IF(COUNTIF(A2:A30,A2)1,"Duplicate","")
to: =IF(COUNTIF(A:A,A2)1,"Duplicate","")

The code for this could then simply be:

ActiveCell.Formula = _
"=IF(COUNTIF(A:A,A" & ActiveCell.Row &
")1,""Duplicate"","""")"

But see a better approach below in the amended code.

The AutoFill part could be:

Range("B2").AutoFill Destination:=Range("B2:B" &
Range("A65536").End(xlUp).Row)

The whole thing turns into:

Range("A:K").Sort _
Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("G2"), Order2:=xlAscending, _
Key3:=Range("F2"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Columns("B:B").Insert Shift:=xlToRight
Range("B2").Formula = _
"=IF(COUNTIF(A:A,A2)1,""Duplicate"","""")"
Range("B2").AutoFill Destination:=Range("B2:B" &
Range("A65536").End(xlUp).Row)

Regards

Trevor


"o1darcie1o" wrote in message
...
I'm trying to set up a macro to do things I normally do by hand,
over
15
or
so tabs in a workbook on a monthly basis.

On each sheet, I sort the sheet according to three columns. Then I
insert
a
column at B. The formula I type by hand is
=IF(COUNTIF(A2:A30,A2)1,"Duplicate",""). Then I double click to
fill
down,
copy, paste values, and sort descending by that new column. Except
that
the
A30 changes each month!

I need to set up the macro so that I can reuse it each month on a
new
workbook. Everything is the same, except the location of the last
row
of
information. I normally use
Selection.AutoFill Destination:=Range("B2:B" &
ActiveSheet.UsedRange.Rows.Count)
and that will auto fill to the last row of used data. But I don't
know
how
to incorporate that into the sort and the end of the range in the if
countif
formula.
It looks like this in my macro:
Range("A1:K30").Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range( _
"G2"), Order2:=xlAscending, Key3:=Range("F2"),
Order3:=xlAscending,
_
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=
_
xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(RC[-1]:R[28]C[-1],RC[-1])1,""Duplicate"","""")"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B30")
Range("B2:B30").Select

Any help or tips for these two things would be most appreciated!!!!