Extracting unique combinations from 2 columns

filthfan

New Member
Joined
Oct 31, 2011
Messages
35
Hi.

Let's say that in sheet One I have a column A with all the dates between July 1st 2011 until January 31st 2012 like seen below.

1.07.2011
2.07.2011
...
31.01.2012

I need to extract from this column only the months and years, ordered in another column, in another sheet Two, on column A, like this:

July 2011
August 2011
...
January 2011

Now this is a bit tricky, because I would need that only the months and years that actually exist in the data in sheet One, column A, so that if I have only 1.10.2011 and 1.01.2012, only October 2010 and January 2012 would be listed.

I would appreciate any help with this, I've been busting my head all day. It would be perfect if I could solve this without using VB scripts.

Many thanks in advance!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
try this,
in A2

=(CHOOSE(TEXT(A1,"mm"),"january","Feb","Mar","Apr","May","June","July","Aug","Sep","Oct","Nov","Dec")&".")&TEXT(A1,"YYYY")


I'm not so good in EXCEL!
 
Upvote 0
Hi, Welcome to MrExcel.

Put this formula in cell A2 of Sheet2.
Press ctrl shift Enter NOT just enter.
You can the copy the formula down until you get #N/A

=TEXT(INDEX(Sheet1!$A$2:$A$551, MATCH(0, COUNTIF($A$1:A1, TEXT(Sheet1!$A$2:$A$551, "mmm-yyyy")), 0)), "mmm-yyyy")

Formula taken from here...
http://www.get-digital-help.com/201...and-months-from-a-long-date-listing-in-excel/

Good luck.

Ak

Hi again.

This formula works perfectly. Still, I would like to use a data range bigger, than the A2:A551 you used, for example A:A, just in case new data is filled in. I modified the range, but it also returns a Jan-1900 value, which is probably the interpretation of the blank cells. Is there a way to get rid of this, too?

Thanks!
 
Upvote 0
Nevermind my last post, I managed to do that using an IF statement an equalizing the initial formula with jan-1900.

Still, I didn't find a way to count how many unique combinations of month-year are there in that range. Any clues?
 
Upvote 0
Hi,

To count the unique months and year on Sheet1 try this....

=SUM(IF(MATCH(DATE(YEAR(Sheet1!$A$2:$A$551), MONTH(Sheet1!$A$2:$A$551), 1), DATE(YEAR(Sheet1!$A$2:$A$551), MONTH(Sheet1!$A$2:$A$551), 1), 0)=ROW(Sheet1!$A$2:$A$551)-MIN(ROW(Sheet1!$A$2:$A$551))+1, 1, 0))

Entered with ctrl Shift Enter NOT just enter.

Good luck

Ak
 
Upvote 0
Hi, Welcome to MrExcel.

Put this formula in cell A2 of Sheet2.
Press ctrl shift Enter NOT just enter.
You can the copy the formula down until you get #N/A

=TEXT(INDEX(Sheet1!$A$2:$A$551, MATCH(0, COUNTIF($A$1:A1, TEXT(Sheet1!$A$2:$A$551, "mmm-yyyy")), 0)), "mmm-yyyy")

Formula taken from here...
http://www.get-digital-help.com/201...and-months-from-a-long-date-listing-in-excel/

Good luck.

Ak

Concerning the formula above, would it be a way to apply this on array A2:A551 (like you did) so that it returns only the values from that array which corespond to one particular value in B2:B551 for example?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top