Macro to fill column with SUMPRODUCT formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Beth - 12 May 2008 22:00 GMT

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

Reply to this Message

Bob Phillips - 12 May 2008 23:27 GMT

Dim LastRow As Long

With ActiveSheet

.Columns(1).Sort key1:=.Range("A1"), header:=xlGuess
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns("C:D").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

Signature

---
HTH

Bob

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

> Hi,
>
[quoted text clipped - 18 lines]
>
> Beth

Reply to this Message

Beth - 14 May 2008 15:52 GMT

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

> Dim LastRow As Long
>
[quoted text clipped - 31 lines]
> >
> > Beth

Reply to this Message

Bob Phillips - 15 May 2008 11:04 GMT

> Hi Bob,
>
[quoted text clipped - 9 lines]
> 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:D").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
[quoted text clipped - 4 lines]
> 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.

Reply to this Message

Beth - 19 May 2008 18:32 GMT

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,
> >
[quoted text clipped - 36 lines]
> It isn't autofilling, it is auto-loading, and anyway, it works bottom up to
> determine the last row, so it ignores those blanks.

Reply to this Message