what version of excel are you using? Excel 2007 has a =SUMIFS function that allows you to sum a column using multiple ranges and criteria. you then copy the first 3 columns to another sheet, sort and remove duplicates (also in Excel2007) You then use =sumifs (the function wizard will help) to add all of the Claim values for the matching year, last and first names.
now, if you're NOT using Excel 2007, I would add a column and drag the formula =A1&A2&A3 down the list. copy these VALUES to another sheet and remove duplicates. Then, use a "singular" =SUMIF to get the combined claim values. let me know if you need a step-by-step example. Paul ________________________________ From: Skin <matthew.bris...@gmail.com> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> Sent: Mon, June 7, 2010 4:47:52 PM Subject: $$Excel-Macros$$ *Question*: Match/Sumif functions to merge rows in in large datasheet I am having some trouble figuring out a problem. Anyway, I am working with a large dataset, so it is obviously not efficient to do this by hand, so hopefully someone can help me out. This is how my spreadsheet is set up: Year|Last Name|First Name|$ amount|Cholesterol Level|Height|Weight However, there are instances in which the same person appears for the same year, but multiple times, and not consecutively. An example is below: Year| Last |First| Claim$ |chol.| ht | wt | 2007|Doe |Jane|$ 350.02 |190 |5'10" |165| 2006|Smith |John|$ 420.99 |165 |5'3" |200| 2007|Doe |Jane|$ 257.12 |190 |5'10" |165| My desired goal is to combine these two rows into one, keeping all values the same *except* the "$ amount" values. I am trying to sum the $ amount. I believe an INDEX or MATCH function must be used, along with a SUMIF function. I need to MATCH the years, last names, and first names, Sum together the claims if they match, and keep the rest of the individuals' data the same. I am working with medical claims data and there are some instances where people have different claims but in the same year, and I need to total them up while keeping all of the other values the same. Can anyone help me out with this? It will save me hours upon hours of work! Thanks! -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
