|
Check rows for duplicate references and add related data
Excel 2002. I have a sheet containing 27K rows of data, each row
referenced by a reference number in column A. Many of the rows have duplicate or triplicate references e.g. 01/010/0002 01/010/0014 01/012/0020 01/012/0020 01/012/0020 01/013/0134 01/014/0004 01/014/0004 but contain different values in subsequent columns. What I need to do is identify the rows where the references are the same and sum data in the corresponding rows e.g. for 01/012/0020 above I need to sum the contents of column C for the 3 related rows and for 01/014/0004 sum the contents of column C for the 2 related rows. I know how to identify duplicates in column A using =countif(A:A,A1) which will give me 2 where there is a duplicate entry or 3 where there is a triplicate entry, but I am not sure where to go from there. I imagine that I need to extract the data to another range to sum the data in column C for each reference. Grateful for any assistance. -- Robert |
Check rows for duplicate references and add related data
Take a look at Data|Subtotals (if your data is in sorted order).
Or even Data|Pivottable. If you want to read more about the pivottable stuff, you may want to look at some links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Robert wrote: Excel 2002. I have a sheet containing 27K rows of data, each row referenced by a reference number in column A. Many of the rows have duplicate or triplicate references e.g. 01/010/0002 01/010/0014 01/012/0020 01/012/0020 01/012/0020 01/013/0134 01/014/0004 01/014/0004 but contain different values in subsequent columns. What I need to do is identify the rows where the references are the same and sum data in the corresponding rows e.g. for 01/012/0020 above I need to sum the contents of column C for the 3 related rows and for 01/014/0004 sum the contents of column C for the 2 related rows. I know how to identify duplicates in column A using =countif(A:A,A1) which will give me 2 where there is a duplicate entry or 3 where there is a triplicate entry, but I am not sure where to go from there. I imagine that I need to extract the data to another range to sum the data in column C for each reference. Grateful for any assistance. -- Robert -- Dave Peterson |
Check rows for duplicate references and add related data
This will work if you can put the result in an adjacent column; you can
modify the approach if you need to: At the end of your columns of data put a column that uses your COUNTIF to count the multiples, but instead of counting the whole column, only count up to the line you are on - thus for row 50 the formula would be: =COUNTIF($A$1:$A50,$A50) Reason: this will count duplicate entries ABOVE the current row, so it will number each one in sequence (1 for the first row of a particular reference, 2 for the second, etc). Now you can identify the first one in each group and on that line calculate your total using a SUMIF, so in the next column over use this formula to sum the total for a particular reference (I will again write it as for row 50, and assume the prior formula is in column D and I want to sum Column C): = IF($D50=1,SUMIF($A:$A,$A50,$C:$C),"") So now once for each reference, on the first row for that particular reference, the total of column C where A is equal to the reference value should be calculated. You could even put in some sort of label to identify it (in an adjacent column): = IF($D50=1,"TOTAL FOR "&$A50&":","") Hope this helps - at least to give some ideas you might be able to use. "Robert" wrote: Excel 2002. I have a sheet containing 27K rows of data, each row referenced by a reference number in column A. Many of the rows have duplicate or triplicate references e.g. 01/010/0002 01/010/0014 01/012/0020 01/012/0020 01/012/0020 01/013/0134 01/014/0004 01/014/0004 but contain different values in subsequent columns. What I need to do is identify the rows where the references are the same and sum data in the corresponding rows e.g. for 01/012/0020 above I need to sum the contents of column C for the 3 related rows and for 01/014/0004 sum the contents of column C for the 2 related rows. I know how to identify duplicates in column A using =countif(A:A,A1) which will give me 2 where there is a duplicate entry or 3 where there is a triplicate entry, but I am not sure where to go from there. I imagine that I need to extract the data to another range to sum the data in column C for each reference. Grateful for any assistance. -- Robert |
Check rows for duplicate references and add related data
Many thanks for the suggestions.
Robert In message , K Dales writes This will work if you can put the result in an adjacent column; you can modify the approach if you need to: At the end of your columns of data put a column that uses your COUNTIF to count the multiples, but instead of counting the whole column, only count up to the line you are on - thus for row 50 the formula would be: =COUNTIF($A$1:$A50,$A50) Reason: this will count duplicate entries ABOVE the current row, so it will number each one in sequence (1 for the first row of a particular reference, 2 for the second, etc). Now you can identify the first one in each group and on that line calculate your total using a SUMIF, so in the next column over use this formula to sum the total for a particular reference (I will again write it as for row 50, and assume the prior formula is in column D and I want to sum Column C): = IF($D50=1,SUMIF($A:$A,$A50,$C:$C),"") So now once for each reference, on the first row for that particular reference, the total of column C where A is equal to the reference value should be calculated. You could even put in some sort of label to identify it (in an adjacent column): = IF($D50=1,"TOTAL FOR "&$A50&":","") Hope this helps - at least to give some ideas you might be able to use. "Robert" wrote: Excel 2002. I have a sheet containing 27K rows of data, each row referenced by a reference number in column A. Many of the rows have duplicate or triplicate references e.g. 01/010/0002 01/010/0014 01/012/0020 01/012/0020 01/012/0020 01/013/0134 01/014/0004 01/014/0004 but contain different values in subsequent columns. What I need to do is identify the rows where the references are the same and sum data in the corresponding rows e.g. for 01/012/0020 above I need to sum the contents of column C for the 3 related rows and for 01/014/0004 sum the contents of column C for the 2 related rows. I know how to identify duplicates in column A using =countif(A:A,A1) which will give me 2 where there is a duplicate entry or 3 where there is a triplicate entry, but I am not sure where to go from there. I imagine that I need to extract the data to another range to sum the data in column C for each reference. Grateful for any assistance. -- Robert |
| All times are GMT +1. The time now is 07:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com