Excel 2010 Programatically match dates

I'm self-employed in the broadcasting business. I use a spreadsheet to schedule the to-air broadcasts.

In the particular worksheet I have a column "Calendar" which displays the date of a scheduled broadcast, which also has an ID in the form of number.

Every broadcast is pre-recorded, often but not always a week prior to its to-air broadcast date. I book the pre-recording date at the same time as I schedule the to-air broadcast. So, I have another column, "Pre-Record" which displays the date of the particular broadcast's pre-recording .

In other words, I enter both dates into a single row, identified by the “Calendar” date. And the pre-recording date is always ahead of the "Calendar" date.

Currently when I look down the Calendar column, I see the broadcast scheduled for each date. But I have to eyeball the sheet to locate the date of its pre-recording. This is risky, so I would like to automate things.

My idea is to have a new column adjacent to the existing Calendar column. This would present the ID of the broadcast that is to be pre-recorded on the day displayed in the Calendar column.

Thus would the particular row defined by the "Calendar" date present the ID of a pre-recording that is to be made on the particular "Calendar" date. Then I could quickly identify the broadcast that is to be pre-recorded on the particular day.

Can I do this programmatically and, if so, what would be the best approach, please?
Who is Participating?
SteveConnect With a Mentor Commented:
Attached is a modified formula.

This has 3 portions:

=Episodes( <date> , <full range of episodes>  , <full range of dates recorded>)

This may work better for you.
Hi there, to best offer a tailored solution, would it be possible to provide a sample workbook.
Feel free to fill it with dummy data, but it is far easier to provide an answer if we have an example file to work from.
Gordon_AtherleyAuthor Commented:
Perfectly fair--will do!
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Attached is an example of what I have in my mind (which could be waaay off in reality)

Is this something close?
Gordon_AtherleyAuthor Commented:
Yes, The_Barman, the Match-Index-Example looks close, thanks.

I'll generate a sample workbook with the example included, and post it back. (I'm not as fast on the code as you, are so I may need a day or so!)
Gordon_AtherleyAuthor Commented:
Attached is the basic xl worksheet

To create a worksheet to enable the talk-show host to keep track of broadcasts and pre-recordings scheduled for a particular day.

1. Col A. The worksheet set out as calendar.
2. Col B the days are named.
3. Col C each Episode is allocated a numeric ID.
4. Col D contains the date of pre-recording; more than one pre-recording can occur on one date.
5.Col E Lists the IDs of the Episodes that are to be pre-recorded on the calendar date in Col A.

Hope this is sufficient to provide the sample, which I've stripped downt to the core requirement.
OK, this one was not as simple as a match index.
So I have created you a function specific to your requirements...

Function Episodes(theDate As Date, theRange As Range) As String
Episodes = ""
For x = 1 To theRange.Rows.Count
    If theRange(x, 2) = theDate Then Episodes = Episodes & " " & theRange(x, 1)
Next x
End Function

Open in new window

The attached workbook has been "spruced up" to include a table and the function.
If you wish to view / move the function press [alt]+[F11] to open the VBA editor.
This can then be coppied into other files as required.
Any questions feel free to ask.
Gordon_AtherleyAuthor Commented:
Thank you. I'll report back shortly.
Gordon_AtherleyAuthor Commented:
Works fine, thank you.


1. When I add a column to the left of but not adjacent to "All Today's Episodes", the latter no longer works. I assume that I have to use the VBA Editor to modify the code. What must I modify, please?

2. I'd like to remove some of the drop-downs that change a column's sort order (I fear confusing myself!). How to remove the drop-downs, please?

1) the range:
If theRange(x, 2) = theDate Then Episodes = Episodes & " " & theRange(x, 1)
The formula looks at the second column for the date: theRange(x, 2)
and returns the firct column the episode: theRange(x, 1)

So if you add something between the Episode and the Date then the '2' in the first bit should be changed to 3 if one column added etc.
Be sure to make sure that the second portion of the formula covers the episodes and the dates. If you make changes and re-post, I can very quickly fix for you.

2) to remove the drop downs, just go datafilter to turn them off
Gordon_AtherleyAuthor Commented:
Thank you, much appreciated.

Apologies about my giving myself the points and grading I intedended for you. I've requested correction.
Gordon_AtherleyAuthor Commented:
Excellent, understandable expertise.
Gordon_AtherleyAuthor Commented:
One last question, please.

Is there any way to automate the following code so that C$46 and $D46 change whenever the column A's row count changes?


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.