Solved Excel: output most recent month from a range of months

April 17, 2014 at 19:07:29
Specs: Windows 7
I have a list of 15 departments, of which one department each month is in charge of a briefing, in no particular rotation. I would like to create an excel cell which outputs the last month that each department held the meeting, and/or if they are due to lead another since they haven't yet.

I have A2:A37 as Jan 2013-Dec 2015
B2:B37 with the name of the department who held it (only B2:B17 are filled)

I used {=IF(COUNTIF(B2:B37,"GenericDeptName"), "Yes","No")} for each cell next to a list of the departments to return Yes if they have held one at all, now I just need to have a separate output of which month/year was the last time they held it.


See More: Excel: output most recent month from a range of months

Report •

#1
April 18, 2014 at 05:04:30

Report •

#2
April 18, 2014 at 07:56:11
It's the latest 2013 version

Report •

#3
April 18, 2014 at 17:25:58
✔ Best Answer
I started with the data shown in Columns A:C.

I array-entered this formula in D2 and dragged it down to D6 to get the results you see in Column D.

Array-Enter via Ctrl-Shift-Enter:

=MAX(IF($B$2:$B$17=C2,$A$2:$A$17))


        A         B          C         D 
1     Month    Held By      Dept     Latest
2    Jan-13	  A	     A	     Mar-14
3    Feb-13	  B	     B	     Feb-14
4    Mar-13	  A	     C	     Apr-14
5    Apr-13	  C	     D	     Oct-13
6    May-13	  E          E       Nov-13
7    Jun-13	  C		
8    Jul-13 	  A		
9    Aug-13	  B		
10   Sep-13	  A		
11   Oct-13	  D		
12   Nov-13	  E		
13   Dec-13	  C		
14   Jan-14	  A		
15   Feb-14	  B		
16   Mar-14	  A		
17   Apr-14	  C
		

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
April 28, 2014 at 13:23:19
Thank you for your response. That is exactly doing what I want it to do, however I can't seem to get it to work after entering it. For example, in D2, the output of Jan-00 is generated, even though the Dept in C2 last held the meeting in B10.

I have Column A and Column D formatted as Date, and B and C as text.

I entered

=MAX(IF($B$2:$B$17=C2,$A$2:$A$17))

as an array and dragged it down. Should I have formatted something differently?


Report •

#5
April 28, 2014 at 14:53:22
My guess is that Column A is not really formatted as Date. Excel sometimes screws up date formats, especially when the dates are imported from another program or a website or copied from another source.

If Excel is not really seeing your dates as chronological dates, then the formula will return a different "MAX" than you would expect.

The best I can offer from afar is to suggest that you manually re-enter your dates in A2:B2, ensuring that they are formatted as Dates by actually setting the format as Date - Mar-12 and trying the formula again.

It works for me when I do it that way but it does not work when I try to copy my data from above and paste it into a worksheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
April 29, 2014 at 02:19:39
Thank you so much for your help, I will try it today, and I'm sure it will work. Much appreciated.

Report •

#7
May 1, 2014 at 07:53:30
That was the perfect solution, I reinput everything and it works fine, thank you.

I used
=IF(MAX(IF($B$3:$B$50=E3,$A$3:$A$50))=0,"",MAX(IF($B$3:$B$50=E3,$A$3:$A$50)))

If I were to have 2 departments hold a meeting in one month, and in the Held By column the department was listed as A / B, is there an easy search string that I could enter to update both dept A and B's latest column?


Report •

#8
May 1, 2014 at 12:27:45
You've got me on that one. I've been trying to come up with a way to use LEFT, RIGHT, and FIND to deal with cells that contain a "/" but nothing that I've tried works.

In concept, I was trying:

If the cell contains a "/" then find the MAX based on the partial string to the LEFT of the "/" and also on the partial string to the RIGHT of the "/". I seem to be able to do one or the other, but not both.

I'll keep trying, but I don't promise much.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
May 2, 2014 at 06:41:35
OK, I think I have a solution, but I can't take full credit for it. I asked for some help with array formulas from some real Excel experts and based on what they suggested, I was able to piece together this formula.

=MAX(MAX(IF(IF(ISERROR(FIND("/",$B$3:$B$50)),LEFT($B$3:$B$50,LEN(E3)),RIGHT($B$3:$B$50,LEN(E3)))=E3,1,0)*$A$3:$A$50),MAX(IF(IF(ISERROR(FIND("/",$B$3:$B$50)),LEFT($B$3:$B$50,LEN(E3)),LEFT($B$3:$B$50,LEN(E3)))=E3,1,0)*$A$3:$A$50))

BTW...It may be possible to find the MAX date for 3, 4, or even more departments by including the MID function and extracting the text between the slashes based on their order within the string (first slash, second slash, third, etc.)

A/B/C/D

I leave that cumbersome task completely in your hands. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Ask Question