VLOOKUP return multiple matches in columns

[Gracie789]

I tried reading through existing posts but can't seem to find anything that fits.

Incoming data is in a worksheet with dates in column A and an event description in column B. There can be multiple events per date and are not in any particular order (they're coming from multiple departments) so it looks like this:

ColA ColB
01/05/2008 MEC Training
06/11/2008 BNB Day Out
03/14/2008 Headcounts Due
01/05/2008 Test backup

I need to get the data into another worksheet that has all dates for the year (Jan 1 through Dec 31 ) in Column A, with any events that match that date in columns B thru Whatever. (could probably safely cap the limit at 250 if necessary)

SHEET2
ColA ColB ColC
01/01/2008
01/02/2008
01/03/2008
01/04/2008
01/05/2008 MEC Training Test Backup

I tried VLookup, then tried to program a function, then found a formula here on this site that works for putting the multiple results in subsequent ROWS and tried to revamp for columns without success.

Any ideas? I'm not that good with Excel, but I can take direction

Thanks is advance!!

[Richard Buttrey]

Hi,

One approach you could take is the following.

First sort the A & B columns by date in ascending order, and make sure the two columns of data start in row 2. Now in C2 enter

=IF(A2<>A3,B2,C3&","&B2)

and in D2

=IF(COUNTIF(A:A,A2)=1,2,COUNTIF(A$2:A2,A2))

copy these formula down the whole set of data, and then Copycolumns B:C and Paste Special values.

Now put some column labels in row 1, A,B,C & D will do, and Autofilter the table picking the value '1' in column D. These will be all the rows, one for every day, and column C will contain all the events, single or multiple for that day separated by commas.

Now all you need do is copy columns A:C of the filtered rows somewhere else, delete column B and do a Data 'Text To Columns' operation on column B using the comma as a delimiter.

HTH

[Gracie789]

Thank you for the reply.

I did try what you suggested and it results in a listing of only those dates that have an event. This is helpful - since the end result needs to have every date in the year, I can use this with a vlookup for the events on Sheet 2.

The only real problem I have with this is that it's a lot of manual work - not that I won't do it this way if I have to, mind you, but I was hoping for a way to work with existing functions to make it automatic. (I.e. if I load the new events file into sheet 2, then sheet 1 automatically updates with all events so I can feed this directly into the next operation) I may get multiple files in a week and it's a pain to spend so much time on something that seems like it ought to be programmable...

Again, thanks for the reply and your thoughts. It may help me as I continue to play with this.

[martindwilson]

if your sheet is laid out as dates in sheet 1 col a info in col b
on sheet 2 put all the days of year down col a and in b1 put

=IF(COLUMN()-1>COUNTIF(Sheet1!$A$1:$A$2000,$A1),"",INDEX(Sheet1!$B$1:$B$2000,SMALL(IF(Sheet1!$A$1:$A$2000=$A1,ROW(Sheet1!$A$1:$A$2000),""),COLUMN()-1)))

confirm with ctrl+alt+enter then drag across and down as required