Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identify the cell with Max & Min values | Excel Worksheet Functions | |||
Auto Identify Values | Excel Discussion (Misc queries) | |||
Identify where max values are found | Excel Discussion (Misc queries) | |||
How can I identify the two lowest values in a row? | Excel Worksheet Functions | |||
identify duplicates | Excel Discussion (Misc queries) |