identify duplicates and sum corresponding values
 

Hi there,

I only have 2 columns in my dataset but i have around 40,000 rows so i need
something relatively quick and easy! ID's are shown in column A and number of
days is shown in column B, there are duplicate ID's in A. I need to sum B
according to column A (i.e. for ID: 1 i need to add B1 and B2 to equal 13).
Can anyone help me with this?

A B
1 5
1 8
2 4
3 6
3 5
3 2

Thanks,
--
Amanda

identify duplicates and sum corresponding values
 

In C1:C10 enter numbers 1 to 10
In D1 enter =SUMIF(A:A,C1,B:B)
Copy this down to D10
I am assuming ID are 1 to 10
I you have loots of unknown ID's use a pivot table
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Amanda" wrote in message
...
Hi there,

I only have 2 columns in my dataset but i have around 40,000 rows so i
need
something relatively quick and easy! ID's are shown in column A and number
of
days is shown in column B, there are duplicate ID's in A. I need to sum B
according to column A (i.e. for ID: 1 i need to add B1 and B2 to equal
13).
Can anyone help me with this?

A B
1 5
1 8
2 4
3 6
3 5
3 2

Thanks,
--
Amanda

identify duplicates and sum corresponding values
 

One quick way is to use a pivot table (PT). Insert a top header row, label
in A1: ID, in B1: Days. Then select any cell within the source table, click
Data PivotTable .... Click Next Next. In step 3, click Layout, then drag
n drop ID within the ROW area, drag n drop Days within the DATA area (it'll
appear as Sum of Days), Click OK Finish. That's it.

The PT will be created in a new sheet to the left, with the required
results, eg:

Sum of Days
ID Total
1 13
2 4
3 13
Grand Total 30

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Amanda" wrote in message
...
Hi there,

I only have 2 columns in my dataset but i have around 40,000 rows so i
need
something relatively quick and easy! ID's are shown in column A and number
of
days is shown in column B, there are duplicate ID's in A. I need to sum B
according to column A (i.e. for ID: 1 i need to add B1 and B2 to equal
13).
Can anyone help me with this?

A B
1 5
1 8
2 4
3 6
3 5
3 2

Thanks,
--
Amanda

identify duplicates and sum corresponding values
 

If column A is sorted, you could also consider Excel's Subtotal feature.

Data/Subtotals




"Amanda" wrote:

Hi there,

I only have 2 columns in my dataset but i have around 40,000 rows so i need
something relatively quick and easy! ID's are shown in column A and number of
days is shown in column B, there are duplicate ID's in A. I need to sum B
according to column A (i.e. for ID: 1 i need to add B1 and B2 to equal 13).
Can anyone help me with this?

A B
1 5
1 8
2 4
3 6
3 5
3 2

Thanks,
--
Amanda

identify duplicates and sum corresponding values
 

That's great... Thanks!
--
Amanda


"Max" wrote:

One quick way is to use a pivot table (PT). Insert a top header row, label
in A1: ID, in B1: Days. Then select any cell within the source table, click
Data PivotTable .... Click Next Next. In step 3, click Layout, then drag
n drop ID within the ROW area, drag n drop Days within the DATA area (it'll
appear as Sum of Days), Click OK Finish. That's it.

The PT will be created in a new sheet to the left, with the required
results, eg:

Sum of Days
ID Total
1 13
2 4
3 13
Grand Total 30

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Amanda" wrote in message
...
Hi there,

I only have 2 columns in my dataset but i have around 40,000 rows so i
need
something relatively quick and easy! ID's are shown in column A and number
of
days is shown in column B, there are duplicate ID's in A. I need to sum B
according to column A (i.e. for ID: 1 i need to add B1 and B2 to equal
13).
Can anyone help me with this?

A B
1 5
1 8
2 4
3 6
3 5
3 2

Thanks,
--
Amanda

identify duplicates and sum corresponding values
 

Good to hear that did it here, Amanda.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Amanda" wrote in message
...
That's great... Thanks!
--
Amanda