Combine rows based on one or two cells

[seagee69]

I'm new to this forum and have been using Excel many years. I have well over 1,000 records in a single sheet containing names, ids, dates, programs, and ratings for students. There are additional columns that also are the same for each student. I can get the basic information I need using pivot tables, but want to generate a printout for others showing a non-duplicated student listing all information about that student, based on the program - combining the ratings into one cell per unique program per student. The rating cell may be blank or it may contain a letter: A, B, C, or etc. The only two areas that may differ for each student listing would be the program and/or the rating. For instance:


A1 Name

B1 StdtID

C1 Date

D1 Program

E1 Rank


A2 Smith, John

B2 1111111

C2 09/16/2012

D2 Computers

E2 A


A3 Smith, John

B3 1111111

C3 09/16/2012

D3 Computers

E3 B


Students may have one or more rows. Students may have different programs, and/or different ratings, including the possibility of multiple ratings in a single program; however, each rating currently is one row. For each student in a particular program, I wish to combine all ratings into one cell, leaving one row per student per program, without having to go through the listing line-by-line.


I have used conditional formatting to identify duplicate students. I've used pivot tables to show those students in more than one program. And I have used a pivot table to show students who had more than one rating. However, that still would require me to manually combine rating values that are the same student for the same program. I have used the conditional formatting to remove full-row duplicates, leaving rows that have at least one difference (program and/or rating).


Hope this makes sense. I have uploaded a sample file showing a few records and line 20 begins the result I hope to accomplish. Since students can have one or more records (rows) the name or stdtid would be used, I think.


Thanks for any assistance.


Sample file: https://dl.dropboxusercontent.com/u/5408020/SampleCombineRating.xlsx

 

[SirJB7]

Hi, seagee69!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


A few days ago I build a similar solution for merging rows and sending mails. Even if it's not exactly what you're asking for, the technique for grouping by many fields (columns) and summarizing attributes can be easily adapted to your requirements. Give a look at this link:

http://chandoo.org/forums/topic/row-merging-based-column-values


Regards!