vlookup related maybe

[sa02000]

I have a list with names in one column and dates in second column. I would like to return all the dates related to a name and then display then horizontally in another spreadsheet against that name matching the month/year in the returned date. So for example if I have this data

Column1 column2
abc dec2006
abc sep2005
def oct2007
abc may2006
ghi may2006
def jan2007
abc dec2005

so the final format should look like

Name sep2005 oct2005 nov2005 dec2005 jan2006 feb2006.....
abc 1 1 .........
def ...................
ghi 1(incolumn heading of may2006)

it doesn't have to be 1 but some sort or marker.....

I can vlookup and return the values but I don't know how to display them horizontally and then match them up with the right mmyy column. Formula or macro is fine for me.
Can anyone help please?

Thanks,
Jay

[Carim]

Hi Jay,

Have a go with a simple pivot table ...

HTH
Carim

[sa02000]

Thanks Carim but I need to do further stuff with this data and pivot table won't have continuous months at the top (which I need for further processing)......

I was trying this
=(IF(VLOOKUP($A2,Current!$P$2:$S$255,4,FALSE)=K$1,1,"2"))

where my name is in $A2 and K$1 has date in mmyy format. The only thing is vlookup returned value has day,month,year and thats why vlookup returned value doesn't match value in K$1. I suppose I can create another column that vlookup is looking up and have only month and year but that will mean addition of another column leading to more memory and slowing down excel.... is there anyway to do it in one formula??

Jay

[Mark@Work]

If all you want is an indicator that a date and name occur together try:

{=SUMPRODUCT(($A2=Sheet1!$A2:$A8)+0,(B$1=Sheet1!$B2:$B8)+0)}

Note that the addresses are what I used to test, replace as appropriate.
The red addresses are your original data columns.
Column A on this new sheet contains the list of names (starting at row 2)
Row 1 contains the list of dates.

Enter this as an Array formula [ctrl shift enter]

Replicate across and down as required.

Mark.

[sa02000]

Mark, for some reason that you supplied didn't work. I checked my references. But finally this seem to be working...

=SUMPRODUCT(--(Current!$P$2:$P$255=$A2),--(MONTH(Current!$S$2:$S$255)=MONTH(K$1)),--(YEAR(Current!$S$2:$S$255)=YEAR(K$1)))

But now I have zeros where there is not match but I don't want those zeros.. is there any way to get rid of those.

Thanks for all the replies.

Jay

[Mark@Work]

Glad its getting there,
The reason the simple version did not work might be due to the way Excel deals with its dates.
Even if you are only displaying Month and Year, Excel is internally including the Day.
This is OK if the Day is the 1st of the month every time, but if it isn't then dates that LOOK identical might not be...

Now to you next problem wrap the whole lot in an IF...
{=IF(SUMPRODUCT(....)=0,"Message 1","Message 2")}

Mark.