generate unique identifier with duplicates

[kst-t]

To all very smart experts,

I have multiple row of dates, each row corresponds to the occurrence of a series of events from column A-K.
e.g. 1/12/2013 21/03/2014 6/11/2013 5/03/2014 1/08/2014

From Column M onwards, each column corresponds to the month and Year
e.g. Dec 2013 Jan 2014 Feb 2014 Mar 2014 Apr 2014

I'm trying to generate unique number for each event from Column M onwards in the same row such that I can highlight the cell using conditional formatting, the final aim of which is to generate a visual timeline. I used the Match function which returned the column numbers. Problematic when two events occurred in the same month/year (e.g. Mar 2014). Please suggest a way to identify that Mar 2014 has events in Column 2 and 4 happened.

Not being overly proficient in Excel, my brain cells are struggling to solve this. ray:ray:THANK YOU SO MUCH FOR DOING THE THINKING FOR ME!

 

[Andrew Poulsom]

Welcome to MrExcel.

If your dates in M1 onwards are serial dates:

=SUMPRODUCT(--(TEXT($A2:$K2,"mmm yyyy")=TEXT(M$1,"mmm yyyy")))

or if they are text entries:

=SUMPRODUCT(--(TEXT($A2:$K2,"mmm yyyy")=M$1))

 

[kst-t]

Many Thanks Andrew for your prompt reply. I must have not explained myself well. Sorry. Here is the screen shot with the formulae (have to change the columns to fit). I want to show Mar 2014 with 2 events differently from Mar 2014 with 1 event. the overlaps of events could be in different combination but with a maximum of 2 events. Many thanks again.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Scenario	Variable 1	Variable 2	Variable 3	Variable 4	Variable 5	Event A	Event B	Event C	Event D	Event E
2	1	15/04/2013	30 days	3 months	Yes	No	1/12/2013	21/03/2014	6/11/2013	5/03/2014	1/08/2014
3
4		Apr 2013	30 days	3 months	Yes	No	Dec 2013	Mar 2014	Nov 2013	Mar 2014	Aug 2014
5
6	2013	2013	2013	2013	2013	2013	2013	2013	2014	2014	2014	2014
7	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
8	1							8				7
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B4	=TEXT(B2,"mmm yyyy")
B8	=IFERROR(MATCH(CONCATENATE(B$7," ",B$6),$B4:$K4,0)," ")
C4	=TEXT(C2,"mmm yyyy")
C8	=IFERROR(MATCH(CONCATENATE(C$7," ",C$6),$B4:$K4,0)," ")
D4	=TEXT(D2,"mmm yyyy")
D8	=IFERROR(MATCH(CONCATENATE(D$7," ",D$6),$B4:$K4,0)," ")
E4	=TEXT(E2,"mmm yyyy")
E8	=IFERROR(MATCH(CONCATENATE(E$7," ",E$6),$B4:$K4,0)," ")
F4	=TEXT(F2,"mmm yyyy")
F8	=IFERROR(MATCH(CONCATENATE(F$7," ",F$6),$B4:$K4,0)," ")
G4	=TEXT(G2,"mmm yyyy")
G8	=IFERROR(MATCH(CONCATENATE(G$7," ",G$6),$B4:$K4,0)," ")
H4	=TEXT(H2,"mmm yyyy")
H8	=IFERROR(MATCH(CONCATENATE(H$7," ",H$6),$B4:$K4,0)," ")
I4	=TEXT(I2,"mmm yyyy")
I8	=IFERROR(MATCH(CONCATENATE(I$7," ",I$6),$B4:$K4,0)," ")
J4	=TEXT(J2,"mmm yyyy")
J8	=IFERROR(MATCH(CONCATENATE(J$7," ",J$6),$B4:$K4,0)," ")
K4	=TEXT(K2,"mmm yyyy")
K8	=IFERROR(MATCH(CONCATENATE(K$7," ",K$6),$B4:$K4,0)," ")
A8	=IFERROR(MATCH(CONCATENATE(A$7," ",A$6),$B4:$K4,0)," ")
L8	=IFERROR(MATCH(CONCATENATE(L$7," ",L$6),$B4:$K4,0)," ")

 

[Andrew Poulsom]

Which cells do you want to format?

 

[kst-t]

I want to format Cell A8 to L8 according to the dates of events. e.g A8, H8 and L8 have different numbers to indicate different events (columns). I used these numbers to create different colours from conditional format to form a timeline. However, at the moment the formulae in A8 to L8 are not doing the job in indicating two events that happened in the same month. e.g. only picking up event D and dropped event B in the month of Mar 2014.

Thanks.

 

[Andrew Poulsom]

What would you like the formula in L8 to return?

 

[kst-t]

I don't mind what number or symbol L8 returns, so long as it uniquely identifies that there are Events B and D happened in that month e.g. BD. If there were Events C and D in that month, it would return with a different number or symbol (i.e. "CD" in this case or a number). I can then apply conditional format the cell according to what it returns with different colours to visually indicate different event or events for different month. Eg. no event = no fill, A with Red, B with Green, AB with Red/Green.

HOpe this is clear and thank you so much Andrew for following my rather lengthy explanation.

 

[Andrew Poulsom]

I know how to count the number of events in a month, but I don't know how to return details of more than one event in a single cell, sorry.

 

[kst-t]

Thank you very much Andrew. I'll have to think of another way of presenting these events.

This webpage has certainly kindled my interest in Excel! learning heaps!Thanks.
Kiu