Combine like columns

Hello all!
I am going to be uploading information into our system but am trying to avoid uploading multiple instances if the information is the same. Here is an example of the table:

A              B              C     D     E                                                     G
2002      2003      ART 101      INTRODUCTION TO ART             3
2003      2004      ART 101      INTRODUCTION TO ART             3
2004      2005      ART 101      ART CLASS 1                                 3
2005      2006      ART 101      ART APPRECIATION                     2
2006      2007      ART 101      ART APPRECIATION                     2

So basically want to say:
2002 - 2004          ART 101  INTRODUCTION TO ART             3
2004 - 2005          ART 101  ART CLASS 1                                  3
2005 - 2007          ART 101  ART APPRECIATION                      2

Thoughts on how I can do this?
Jason BentleyAsked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
Changed.
29089975A.xlsm
0
 
Chris JohnstonCommented:
This is a great question. I am thinking that you may need a macro. That is over my head. But check out how to use the Excel IF statement here. Because I will try to solve it along with you. I just don't know how to use it to say if two columns match in one cell take the high and low from other cells and create one row.
0
 
Martin LissOlder than dirtCommented:
Run the Consolidate macro you'll find in Module 1.
29089975.xlsm
1
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Rob HensonFinance AnalystCommented:
If you are happy with the result in a different format, you could make use of Pivot Table.

The result would look something like:
                                                                                  Min Date            Max Date
ART 101  INTRODUCTION TO ART             3        2002                    2004
ART 101  ART CLASS 1                                 3        2004                    2005
ART 101  ART APPRECIATION                     2        2005                    2007
0
 
Jason BentleyAuthor Commented:
Thanks everyone for your help. This got me going in the right direction.

Martin - I did have a question for you about the macro. I am wanting to alter the macro slightly by putting the year high in column A and year Low in column B (instead of having them both in A separated by a " - ". I was looking at the code and wasnt quite sure where to add this.

I also wanted to add if the credits (column G) change to add a new line.

Thanks!
0
 
Martin LissOlder than dirtCommented:
Please explain what you mean by
I also wanted to add if the credits (column G) change to add a new line.
0
 
Jason BentleyAuthor Commented:
We were saying in the first test run that if the course name (column E) changes, put the low year - high year of when that information was the same, then a new line of new course name with new year low - year high

Looking to do the same thing with an added rule of if the credits (column G) changes. Here is an example:
A              B              C     D     E                                                       G
2002      2003      ART 101      INTRODUCTION TO ART             3
2003      2004      ART 101      INTRODUCTION TO ART             3
2004      2005      ART 101      INTRODUCTION TO ART             3                
2005      2006      ART 101      INTRODUCTION TO ART             2
2006      2007      ART 101      ART APPRECIATION                     2

When condensed it should look like this:
A              B              C     D     E                                                       G
2002      2005      ART 101      INTRODUCTION TO ART             3
2005      2006      ART 101      INTRODUCTION TO ART             2
2006      2007      ART 101      ART APPRECIATION                     2
0
 
Rob HensonFinance AnalystCommented:
That would also be doable with the Pivot table suggestion that I submitted, we would just include the grade as a row header. This method also separates the years into separate columns as earlier requested, only difference being it puts the years after the headers.

I did notice that the course level (Introduction, Art 1, Art Appreciation) do end up in alphabetical order rather than sequential.
0
 
Jason BentleyAuthor Commented:
Rob, I need the output to be the simplest format because I am going to save it as a csv file to be uploaded into our system. It has to be in a format with no bells or whistles so it can be properly read during the upload.
0
 
Martin LissOlder than dirtCommented:
Does my update do what you want?
0
 
Jason BentleyAuthor Commented:
Testing it now to see
0
 
Jason BentleyAuthor Commented:
Martin,

It worked pretty well! I made a few changes to the VBA to account for Variable credits that caused it to stop but other than that it worked great! Thank you for your help!
0
 
Chris JohnstonCommented:
@Rob Henson is there a way to use the pivot table method and have a row that is consistent with your flattened result listed in another row? For instance, I could flatten just about all the data. However, I could not find a way to make a column that is not numeric display. It seems like the reason I cannot is that I don’t know how to represent it (ART) without a summarizing value. Also summarizing the values for things like LEVEL and others works but is not necessary because it just needs to carry over. Note: I made the list a little bigger to make a better test list I realize @Jason Bentley was only showing a small sample of some data.

pivFlat.png
0
 
Rob HensonFinance AnalystCommented:
What should the subject column be showing?

If that column in the source data is text then it can't be used as a Value field, it can only used as a Row or Column header.
0
 
Chris JohnstonCommented:
The SUBJECT.
testList.jpgare you saying there is no way to carry it over as just text?  Excuse me... my above post I said "listed in another row" I meant column. Thanks
0
 
Rob HensonFinance AnalystCommented:
With the pivot layout screen you showed earlier, the Subject is in the Value pane (bottom right); drag it to the Row pane (bottom left). The order of the values in the pane will affect the order of row headers.

You will then get rows for each subject.

When you have multiple entries per row label standard formatting will put blank cells, ie not repeat label headers. With the cursor in the pivot table you will have extra tabs on the ribbon, one called Layout. One of the buttons on that ribbon gives the option to repeat label headers.
0
 
Chris JohnstonCommented:
@Rob Henson had to click "Show in Tabular Form. Thank you so much... and thanks for asking the question @Jason Bentley this is going to be so helpful. @Martin Liss I love your solution also the problem is I am the only one here that understands a macro. Thanks
0
 
Rob HensonFinance AnalystCommented:
Chris - nothing there.

Whose question is this? Chris Johnson or Jason Bentley???

Whoever, a sample file would be a good idea.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.