Adding like rows
 

I use Excel 2003. I have a 100 row spreadsheet with 3 columns:
Amount, Value A & Value B. What is the easiest way to create a second
3 column spreadsheet where each row is unique Value A - Value B
combination and the 3rd column is the total of every amount in the
original spreadsheet with that particular A-B value.

To illustrate with a simple example, if I start with:

$500 10 265
$200 10 265
$100 20 300
$300 20 300

I want this result:

$700 10 265
$400 20 300

Thanks very much,
Dave

Adding like rows
 

One simple formulas play which should deliver it for you

To keep things neat, let's just take it out in adjacent cols on the same
sheet
(it'll work as well in another sheet)

Assume source data is in cols A to C, from row 2 to 200

In E2:
=IF(B2="","",IF(SUMPRODUCT((B$2:B2=B2)*(C$2:C2=C2) )1,"",ROW()))
Leave E1 blank

In F2:
=IF(OR(G2="",H2=""),"",SUMPRODUCT((B$2:B$200=G2)*( C$2:C$200=H2),A$2:A$200))

In G2:
=IF(ROWS($1:1)COUNT($E:$E),"",INDEX(B:B,SMALL($E: $E,ROWS($1:1))))
Copy G2 to H2. Select E2:H2, copy down to H200. Minimize/hide away col E.
Cols F to H will return the exact results that you seek, all neatly bunched
at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
...
I use Excel 2003. I have a 100 row spreadsheet with 3 columns:
Amount, Value A & Value B. What is the easiest way to create a second
3 column spreadsheet where each row is unique Value A - Value B
combination and the 3rd column is the total of every amount in the
original spreadsheet with that particular A-B value.

To illustrate with a simple example, if I start with:

$500 10 265
$200 10 265
$100 20 300
$300 20 300

I want this result:

$700 10 265
$400 20 300

Thanks very much,
Dave

Adding like rows
 

Hey Max,
You're a genius. Your formulas worked beautifully.
Thank you!
Dave







On May 13, 4:47 pm, "Max" wrote:

One simple formulas play which should deliver it for you

To keep things neat, let's just take it out in adjacent cols on the same
sheet
(it'll work as well in another sheet)

Assume source data is in cols A to C, from row 2 to 200

In E2:
=IF(B2="","",IF(SUMPRODUCT((B$2:B2=B2)*(C$2:C2=C2) )1,"",ROW()))
Leave E1 blank

In F2:
=IF(OR(G2="",H2=""),"",SUMPRODUCT((B$2:B$200=G2)*( C$2:C$200=H2),A$2:A$200))

In G2:
=IF(ROWS($1:1)COUNT($E:$E),"",INDEX(B:B,SMALL($E: $E,ROWS($1:1))))
Copy G2 to H2. Select E2:H2, copy down to H200. Minimize/hide away col E.
Cols F to H will return the exact results that you seek, all neatly bunched
at the top
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
wrote in message

...

I use Excel 2003. I have a 100 row spreadsheet with 3 columns:
Amount, Value A & Value B. What is the easiest way to create a second
3 column spreadsheet where each row is unique Value A - Value B
combination and the 3rd column is the total of every amount in the
original spreadsheet with that particular A-B value.

To illustrate with a simple example, if I start with:

$500 10 265
$200 10 265
$100 20 300
$300 20 300

I want this result:

$700 10 265
$400 20 300

Thanks very much,
Dave

Adding like rows
 

Welcome, Dave
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
...
Hey Max,
You're a genius. Your formulas worked beautifully.
Thank you!
Dave