• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

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?
0
Gordon_Atherley
Asked:
Gordon_Atherley
  • 8
  • 5
1 Solution
 
SteveCommented:
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.
0
 
Gordon_AtherleyAuthor Commented:
Perfectly fair--will do!
0
 
SteveCommented:
Attached is an example of what I have in my mind (which could be waaay off in reality)

Is this something close?
Match-Index-Example.xlsx
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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!)
0
 
Gordon_AtherleyAuthor Commented:
Attached is the basic xl worksheet

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

Explanation
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.
FCGU-test-filev3.xlsx
0
 
SteveCommented:
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.
FCGU-test-filev3.xlsm
0
 
Gordon_AtherleyAuthor Commented:
Thank you. I'll report back shortly.
0
 
Gordon_AtherleyAuthor Commented:
Works fine, thank you.

Queries

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?

Gordon
0
 
SteveCommented:
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
0
 
SteveCommented:
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.
FCGU-test-filev3.xlsm
0
 
Gordon_AtherleyAuthor Commented:
Thank you, much appreciated.

Apologies about my giving myself the points and grading I intedended for you. I've requested correction.
0
 
Gordon_AtherleyAuthor Commented:
Excellent, understandable expertise.
0
 
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?

=Episodes(Sheet1!$A2,Sheet1!$C$2:$C$46,Sheet1!$D$2:$D$46)

Gordon
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now