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!
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

mmmarks

Active Member
Joined
Jun 4, 2011
Messages
427
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!
 

filthfan

New Member
Joined
Oct 31, 2011
Messages
35

ADVERTISEMENT

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!
 

filthfan

New Member
Joined
Oct 31, 2011
Messages
35
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?
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911

ADVERTISEMENT

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
 

filthfan

New Member
Joined
Oct 31, 2011
Messages
35
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,517
Members
414,074
Latest member
Matthew Kakde

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
Top