Hi,

I have an Excel Spreadsheet like below:

COL A             COL B
1670              152
1675              256
1670              148
1672              642
1675              748
1670              364

I am looking for a way to find all the matches within in column A
and calculate all the numbers corresponding in column B 

Desired Result
COL A             COL X
1670              664
1672              642
1675              1004

Thanks

---

Request for Question Clarification by answerguru-ga on 20 Nov 2005 17:00 PST

Hi there,

In your example, you have a value in Column A (1670) that appears
three times and pair with different numbers, but in the result only
the first one is displayed in Column X. You also say you want to
'calculate all the numbers corresponding in Column B'. These
statements are conflicting - could you please clarify what you want
exactly?

Secondly, in your 'desired result' I notice the first column is sorted
- is this a requirement?

Thirdly, I am assuming that if Column A and B are in one spreadsheet,
Column A and X would be output to another spreadsheet. Is this
correct?

Also, there isn't a built in function that can do what you are asking
so the answer would likely be to program a macro doing what you
describe. Is this a reasonable solution for you?

Based on your responses, I can let you know if the list price is
reasonable for what you are asking.

Thanks,
answerguru-ga

---

Clarification of Question by bremar-ga on 21 Nov 2005 00:39 PST

Thanks for your quick response answerguru-ga!

I am simply trying to calculate all the values in COL B, which have
the same value in COL A.
So the Value of (1670) appears 3 times in COL A, and has the
corresponding values of (152),(148),(364). I am after the sum of these
values which is 664.

Sorting is not a requirement.

In regards to my desired result, the placement of the New COL A and
COL X, can be on the same spreadsheet or a different workbook. This is
not important to me. Macro or formula is fine.

Thanks answerguru-ga

Hi bremar-ga,

Based on your original sample and the clarification you have provided,
I have written a function in VBA which will allow you to sum the
values in column B which all have the same value in column A.

The text for this function is listed below:

' Function starting

Function SumAssociatedValue(ByVal ref As Integer, ByVal range As range) As Integer

Dim sum As Integer
sum = 0
Dim cell As range

For Each cell In range

    If cell.Column = 1 And cell.Value = ref Then
        sum = sum + range.Cells(cell.Row, 2)
    End If

Next cell

SumAssociatedValue = sum

End Function

' Function Over

 In order to use the function, you will first need to copy it into a
module within the file you are planning to use it. The steps to do
this are as follows:

 1. Open your spreadsheet and go to Tools > Macro > Macros
 2. Select Edit to display the Visual Basic editor
 3. On the left panel, double click on a module to open it (by default
there should be a module called Module1)
 4. A text window will be displayed (empty unless you have anything
else in that module)
 5. Paste the function text into the module
 6. Select File > Save to save the changes

You can call the function as follows:

 =SumAssociatedValue(lookupValue,range)

lookupValue: This is the value in Column A for which you want the sum
of the associated values
range: This must be a two-column range (specifically columns A and B
as in your example) with as many rows as you like.

So, using your original example the following call:

 =SumAssociatedValue(1670,A1:B6)

will yield a result of 664.

If you have any problems or questions regarding the information above,
please do let me know by requesting clarification prior to rating the
answer.

Cheers!

answerguru-ga

---

Request for Answer Clarification by bremar-ga on 21 Nov 2005 13:42 PST

Thanks answerguru-ga

Having problems with running the Macro. I followed the following steps:

Open your spreadsheet and go to Tools > Macro > Macros
 2. Select Edit to display the Visual Basic editor
 3. On the left panel, double click on a module to open it (by default
there should be a module called Module1)
 4. A text window will be displayed (empty unless you have anything
else in that module)
 5. Paste the function text into the module
 6. Select File > Save to save the changes

I called the macro 'TESTS' and saved it, (As it wouldn't let me call
it  =SumAssociatedValue(lookupValue,range)

But it does not appear in my MACRO lists to run it?

Sorry, I am not real experienced with macros

Brett

---

Clarification of Answer by answerguru-ga on 21 Nov 2005 14:56 PST

Hi there,

This is actually a function, not a macro. By saving it in the module
(as long as your module is in the same file as your workbook) you will
be able to access it just like any other worksheet function that is
built into Excel.

I noticed that mdw811-ga has commented with an alternative approach,
however he/she is not an official Google Answers Researcher and can
only comment on questions (not actually answer them). You can spot a
researcher by the hyperlink attached to our names on posts.

If there is anything else I can assist with in getting the function
properly situated in your workbook please let me know and I'd be happy
to help.

Thanks,
answerguru-ga

---

Clarification of Answer by answerguru-ga on 21 Nov 2005 15:06 PST

I looked at your steps again and I wanted to ensure you understood the process.

Once you have pasted the function into the Visual Basic Editor, you
just need to save within the editor (File > Save myFile.xls).

What I meant by 'calling' the function was to actually place this text:
=SumAssociatedValue(lookupValue,range)

into the cell in your worksheet where you want the result to display.
So, the text you would actually place in your worksheet cell would
look like this:

=SumAssociatedValue(1670,A1:B6)

Something that you can call from within a cell in Excel is known as a
function, whereas a custom procedure which is invoked through a menu
command is called a macro. Hope that clears things up!

Cheers,
answerguru-ga

An Excel pivot table would do what you're asking.  

Highlight your table
Select Data:Pivot Table
Select Next
Verify the range
Select Next
Select Layout
Drag Col A into the row area
Drag Col B into the data area
double click Col B to change the data to SUM
OK
Select the location for the output table
Click finish

Hi mdw811-ga 

Nice and simple, 
worked great....thanks

How do I confirm you answered the question through Google.

Mine was only a 'comment'  I'm not one of the paid researchers.  I
just thought the answer was a little more than what was needed.

Good luck

Many thanks mdw811-ga!!

Here's *another* fairly simple way to get your desired result:

Select column A and go to Data > Filter > Advanced Filter. Click on
'Copy to another location' and 'Unique records only', and enter D1 in
the Copy to: box. (I'm assuming you have row 1 for headings and the
data starts at row 2). This will give you the values from A with the
duplicates removed.
Then, in cell E2, enter =sumif(A:A,D2,B:B), and fill this formula down
to the end of the data.

I personally prefer this method because I like SUMIF and I don't like
PivotTables. Your mileage may vary a bit. ;-)

SUMIF(source, criterion, target) adds together all cells in the target
range that correspond to cells in the source range matching the given
criterion. In this case we're comparing all the cells in column A
against the given value in D2 (or D3 or ...) and for the ones that
match, adding together the corresponding cells in column B. It's quite
a nice function - there's also a COUNTIF(source, criterion) function
if you just need to know how many cells match.

The beauty of COUNTIF/SUMIF is that the criterion doesn't have to be a
cell - it can also be a string expression, and you can use this to
good effect if you need a complicated data-dependent match.
Particularly useful for inequalities - you can do something like
"=SUMIF(A:A, ">"&ROUND(B2*0.25+C2, 2), D:D)", for instance.

This is getting pretty far off the topic, but SUMIF also works much
faster than doing a multi-criterion match using SUM(IF(...)). One
technique I've used quite often when I need to match on multiple
conditions is to add a Key column to one worksheet, containing all the
key data concatenated together (delimited by something like ||). Then
I can use COUNTIF, SUMIF and VLOOKUP on the other sheet to find the
appropriate data row(s) (e.g. =COUNTIF(Sheet1!Keys, A2&"||"&B2)). If I
need to match both ways I add a Key column to both worksheets, and
then I can match directly on the key columns.

bremar-ga 

All the above are either wrong or too over done.  The solution to get
your desire result is a simple 5 click process.  And that applies to 6
pair of number or 10,000 pairs of numbers.

Click:
Select, Data, then select, Pivot Table
Select, Multiple Consolidated Range
Select, Create a single page field for me, Next
Highlist the range A1:B6 (or what ever long), next
Select, Existing Worksheet, finish

Good luck

gjsfus

Thanks gjsfus!

Nice and simple