Extracting all unique combinations of elements across three columns

Hello,

I have a file with two important tabs. "compiler" and "annual summary".

In compiler there is a bunch of data entered into columns. Three of these columns are "Year", "Location" and "Crude Type". The data is collected on a monthly basis.

I wish to write a program that, among other things, will first (after naming the ranges) as "year", "Location" and "Crude", respectively, all unique combinations across the three columns in the compiler sheet into the annual summary sheet with the elements still organized into separate columns.

For example,

In the "year" column, there are elements "2012" & "2013", in the "location column" the elements "regina" and "Prince Rupert", among others exist, and in "crude" there are entries of "WCB" and "OCC" among others. Lets say they appear like this:

2012 Prince Rupert WCB
2012 Prince Rupert WCB
2013 Regina OCC
2013 Prince Rupert WCB

In the annual summary tab I want the output to be:

2012 Prince Rupert WCB
2013 Regina OCC
2013 Prince Rupert WCB

In my actual sheet there are many other values that exist in these columns, and I have attached a sample excel sheet as a more clear demonstration. Any help in creating a code that will accomplish this task is greatly appreciated. I have put 0s as placeholders where other data would be in the original file.

Thanks in advance.

Hi,

This does help, but there is still one glitch. It works fine for outlining the year and location, but I can't separate the crude type as well.

So using this method, it is easy to extract the first four characters in the unique combinations as being the year (using the left function), but then there are also the location AND crude type variables to be retrieved.

So, for example, my first entry in the compiler tab that results from using the Index,Match,Countif formula (which you put in column J) is actually supposed to be:

2012 Regina WCB

Another unique entry is:

2013 Montreal WCB

Not sure how to use the Left or Right formulas to extract what i need given that the number of characters in the elements of the location and crude type columns change throughout the array.

Any thoughts?

This is perfect. Thank you so much for your help.

Now if only I could learn how to code these things into VBA. I just started trying to learn haha. It's proving to be quite a task!

Thanks again.

hi bakeraj256, VB option, press "Run" button on Annual Summary sheet or run code "test"

Thank you both.

Yes, I can definitely use Glenn's suggestion.

Am working now on adapting Watersev's file to the setup of the actual file I am working on.

Will do as asked in Glenn's 16:41pm (Ontario time) post.