Macro to fill column with SUMPRODUCT formula
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,"Exi st","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 |
Macro to fill column with SUMPRODUCT formula
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,""E xist"",""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" wrote in message ... 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,"Exi st","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 |
Macro to fill column with SUMPRODUCT formula
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" wrote: 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,""E xist"",""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" wrote in message ... 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,"Exi st","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 |
Macro to fill column with SUMPRODUCT formula
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 "Bob Phillips" wrote: "Beth" wrote in message ... 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:D").Insert .Range("C2").Resize(LastRow - 1).Formula = _ "=IF(SUMPRODUCT((A$2:A2=A2)+0,(B$2:B2=B2)+0)=1,""E xist"",""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. |
All times are GMT +1. The time now is 03:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com