By Andy L Gibson

Some of the most frequently used tools in Excel are formulas to calculate values from a list, such as totals and averages.

But sometimes you need to ignore certain values such as duplicates in the calculation and this article shows you how to create a formula which ignores duplicate numbers, using a few lines of standard VBA code.

Creating The Syntax For The Formula

We'll use an example of a list of numbers where you need the average of the unique values only.

Numbers

12

12

13

10

20

12

In the real world, your situation may be different; it might be a list of names with linked values, or perhaps you need to ignore duplicated ID numbers but the concept is the same.

The syntax for the formula to average a list is placed in any cell, and is shown below.

=AVERAGE(A2:A6)

The difficulty is that we need to ignore certain cells, but luckily we can write the formula as a comma delimited list to select specific addresses.

=AVERAGE(A2,A3,A4,A5)

All we need now is some VBA code to identify unique values and use those cell addresses in our formula.

First, we'll select the range of cells and create two string variables.

Dim rng As Range

Set rng = ActiveCell.CurrentRegion

Dim vals As String

Dim adds As String

vals = ","

Now, we loop through the cells. Using the "instr" command, we add only unique numbers to the "vals" string and create a list of the cell addresses in the "adds" string.

vals = ","

For x = 2 To rng.Rows.Count

If InStr(vals, "," & rng.Rows(x) & ",") = 0 Then

vals = vals & rng.Rows(x) & ","

adds = adds & rng.Rows(x).Address & ","

End If

Next

' remove the final comma

adds=mid(adds,1,len(adds)-1)

The string adds gives us a value of "$A$2,$A$4,$A$5,$A$6" which we can then use as a range in our formula.

Optionally, we've selected the range so we can see the cells selected, then we've created the formula in the cell below the final number.

Range(adds).Select

Range("a" & rng.Rows.Count + 1).Formula = "=average(" & adds & ")"

Because this procedure finds the unique values first, the formula can then be any of the standard calculations provided in Excel, such as totals and minimums.

The challenge may be to use the code in other situations. For example, you may need to ignore values associated with duplicated ID numbers in which case you can use the offset command to get the cell address of the value rather than the ID.

Summary

This code highlights how you can use standard VBA code to improve the functionality of an existing Excel tool. Being able to fine tune your spreadsheet in just a few minutes can dramatically improve your productivity and efficiency.

Andy L Gibson is a an Excel developer and writer keen to bring the magic of VBA to non-programmers. His blog at http://solutions4business.wordpress.com/ holds the VBA code for all of his articles on Excel and is available for FREE download.

Some of the most frequently used tools in Excel are formulas to calculate values from a list, such as totals and averages.

But sometimes you need to ignore certain values such as duplicates in the calculation and this article shows you how to create a formula which ignores duplicate numbers, using a few lines of standard VBA code.

Creating The Syntax For The Formula

We'll use an example of a list of numbers where you need the average of the unique values only.

Numbers

12

12

13

10

20

12

In the real world, your situation may be different; it might be a list of names with linked values, or perhaps you need to ignore duplicated ID numbers but the concept is the same.

The syntax for the formula to average a list is placed in any cell, and is shown below.

=AVERAGE(A2:A6)

The difficulty is that we need to ignore certain cells, but luckily we can write the formula as a comma delimited list to select specific addresses.

=AVERAGE(A2,A3,A4,A5)

All we need now is some VBA code to identify unique values and use those cell addresses in our formula.

First, we'll select the range of cells and create two string variables.

Dim rng As Range

Set rng = ActiveCell.CurrentRegion

Dim vals As String

Dim adds As String

vals = ","

Now, we loop through the cells. Using the "instr" command, we add only unique numbers to the "vals" string and create a list of the cell addresses in the "adds" string.

vals = ","

For x = 2 To rng.Rows.Count

If InStr(vals, "," & rng.Rows(x) & ",") = 0 Then

vals = vals & rng.Rows(x) & ","

adds = adds & rng.Rows(x).Address & ","

End If

Next

' remove the final comma

adds=mid(adds,1,len(adds)-1)

The string adds gives us a value of "$A$2,$A$4,$A$5,$A$6" which we can then use as a range in our formula.

Optionally, we've selected the range so we can see the cells selected, then we've created the formula in the cell below the final number.

Range(adds).Select

Range("a" & rng.Rows.Count + 1).Formula = "=average(" & adds & ")"

Because this procedure finds the unique values first, the formula can then be any of the standard calculations provided in Excel, such as totals and minimums.

The challenge may be to use the code in other situations. For example, you may need to ignore values associated with duplicated ID numbers in which case you can use the offset command to get the cell address of the value rather than the ID.

Summary

This code highlights how you can use standard VBA code to improve the functionality of an existing Excel tool. Being able to fine tune your spreadsheet in just a few minutes can dramatically improve your productivity and efficiency.

Andy L Gibson is a an Excel developer and writer keen to bring the magic of VBA to non-programmers. His blog at http://solutions4business.wordpress.com/ holds the VBA code for all of his articles on Excel and is available for FREE download.

You have read this article

**Computers and Technology**/**Programming**with the title**How To Use VBA To Calculate Values In Excel From A List Containing Duplicates**. You can bookmark this page URL**http://rollycoasty.blogspot.com/2012/12/how-to-use-vba-to-calculate-values-in.html**. Thanks!
My spouse and I stumbled over here different web page and thought I might check things out.

ReplyDeleteI like what I see so i am just following you. Look forward to exploring your web page again.

Also see my web page:alta white