Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Fill Cells With Formula; Conditional To Seperate Column | Excel Discussion (Misc queries) | |||
fill column with formula | Excel Worksheet Functions | |||
Fill a column with a formula | Excel Discussion (Misc queries) | |||
sumproduct-can fill down change column reference? | Excel Worksheet Functions | |||
Fill column macro that stops | Excel Programming |