Macro to fill column with SUMPRODUCT formula

[Beth]

Hi,

I'm new to macros. How do I create a macro that sorts by Column A, inserts
two new columns after column B (i.e. they will become column C & D) and fill
column C & Column D with these formulas down to the last row in the current
sheet ?

Formula assumes that column headers are in first row.

Column C: =IF(SUMPRODUCT((A$2:A2=A2)+0,(B$2:B2=B2)+0)=1,"Exist","Duplicate")

Column D:
=SUMPRODUCT(($A$2:$A$2=A2)*($C$2:$C$2="Exist"))

This will really save me time since I have to do this task on many sheets.

TIA

Beth

 

[Bob Phillips]

Dim LastRow As Long

With ActiveSheet

.Columns(1).Sort key1:=.Range("A1"), header:=xlGuess
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns("C").Insert
.Range("C2").Resize(LastRow - 1).Formula =
"=IF(SUMPRODUCT((A$2:A2=A2)+0,(B$2:B2=B2)+0)=1,""Exist"",""Duplicate"")"
.Range("D2").Resize(LastRow - 1).Formula =
"=SUMPRODUCT(($A$2:$A$2=A2)*($C$2:$C$2=""Exist""))"
End With


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Beth]

Hi Bob,

Thanks, this is a big help! But I made a wrong assumption on my formula so
what is needed for column D is
SUMPRODUCT(($A$2:$A$[lastrow]=A2)*($C$2:$C$[lastrow]=""Exist"")). The
[lastrow] is the last row in the sheet and differs from sheet to sheet I run
the macro on. I also need the sorting to be by Column A & B now (I might not
have been too clear about this in my first post but... I want to sort the
entire sheet/selection based on Column A & B, and not just sorting the values
within those two columns).

I also noticed the autofill of the formulas stops whenever cell(s) in column
A or B is blank. For a row, it's possible for Column A and/or B to be blank
while Column E onwards still contain data. Is there a way for the autofill to
fill in the formulas until the actual last row ? (You may assume row E as
always containing data).

Appreciate your help and sorry for causing the rework on the code.

TIA

Beth

 

[Bob Phillips]

Hi Bob,

Thanks, this is a big help! But I made a wrong assumption on my formula so
what is needed for column D is
SUMPRODUCT(($A$2:$A$[lastrow]=A2)*($C$2:$C$[lastrow]=""Exist"")). The
[lastrow] is the last row in the sheet and differs from sheet to sheet I
run
the macro on. I also need the sorting to be by Column A & B now (I might
not
have been too clear about this in my first post but... I want to sort the
entire sheet/selection based on Column A & B, and not just sorting the
values
within those two columns).

Dim LastRow As Long

With ActiveSheet

.Cells(1).Sort key1:=.Range("A1"), key2:=.Range("B1"),
header:=xlGuess
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns("C").Insert
.Range("C2").Resize(LastRow - 1).Formula = _
"=IF(SUMPRODUCT((A$2:A2=A2)+0,(B$2:B2=B2)+0)=1,""Exist"",""Duplicate"")"
.Range("D2").Resize(LastRow - 1).Formula = _
"=SUMPRODUCT(($A$2:$A$" & LastRow & "=A2)*($C$2:$C$" & LastRow &
"=""Exist""))"
End With

I also noticed the autofill of the formulas stops whenever cell(s) in
column
A or B is blank. For a row, it's possible for Column A and/or B to be
blank
while Column E onwards still contain data. Is there a way for the autofill
to
fill in the formulas until the actual last row ? (You may assume row E as
always containing data).

It isn't autofilling, it is auto-loading, and anyway, it works bottom up to
determine the last row, so it ignores those blanks.

 

[Beth]

Thank you. Since the auto-loading is working from bottom up, I changed this
line so the LastRow is obtained from the column filled with data up to the
actual last row:

LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row

Beth

Hi Bob,

Thanks, this is a big help! But I made a wrong assumption on my formula so
what is needed for column D is
SUMPRODUCT(($A$2:$A$[lastrow]=A2)*($C$2:$C$[lastrow]=""Exist"")). The
[lastrow] is the last row in the sheet and differs from sheet to sheet I
run
the macro on. I also need the sorting to be by Column A & B now (I might
not
have been too clear about this in my first post but... I want to sort the
entire sheet/selection based on Column A & B, and not just sorting the
values
within those two columns).

Dim LastRow As Long

With ActiveSheet

.Cells(1).Sort key1:=.Range("A1"), key2:=.Range("B1"),
header:=xlGuess
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns("C").Insert
.Range("C2").Resize(LastRow - 1).Formula = _
"=IF(SUMPRODUCT((A$2:A2=A2)+0,(B$2:B2=B2)+0)=1,""Exist"",""Duplicate"")"
.Range("D2").Resize(LastRow - 1).Formula = _
"=SUMPRODUCT(($A$2:$A$" & LastRow & "=A2)*($C$2:$C$" & LastRow &
"=""Exist""))"
End With

I also noticed the autofill of the formulas stops whenever cell(s) in
column
A or B is blank. For a row, it's possible for Column A and/or B to be
blank
while Column E onwards still contain data. Is there a way for the autofill
to
fill in the formulas until the actual last row ? (You may assume row E as
always containing data).

It isn't autofilling, it is auto-loading, and anyway, it works bottom up to
determine the last row, so it ignores those blanks.