Excel HLOOKUP - date range matching and highligting

I have a list of people in one table, with a destination for their holiday, and a date ranges next to each one. For instance:

Bill Brown        Turkey        01/05/2011 04/05/2011
Bill Brown        Sweden       05/06/2011 10/06/2011
John Doe        France         07/06/2011 12/06/2011
Bill Brown        Australia      09/06/2011 15/06/2011
John Doe        Wales          11/06/2011 18/06/2011

What I want is to use this table to populate a second table.

The second table would list all the people down the left hand side, and then have a column for each day with the dates listed along the top.

I want the second table to make the relevant cell turn green when they are on holiday, but red if they are trying to be on two holidays at the same time. That should give me a grid of people, showing when they are on holiday, and highlighting which holidays clash.

I think its to do with hlookup etc, but I'm stuck after that!
benlintonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rspahitzCommented:
Have you looked into Pivot tables?  They're great for summary information.

For example, you can get it to show the employees along the left and dates along the top and show the count of items that match in the intersecting grid area.  If the count is >1, then you have a conflict.
0
benlintonAuthor Commented:
That sounds very promising - how do I go about that?

Also, is it possible to enter the locations that clash, perhaps as some sort of comment - I've uploaded an attachment which shows the ideal...

I'm guessing that I'll end up with about 1000 holidays and 100 people eventually. holiday-planner.xls
0
byundtMechanical EngineerCommented:
Here is a macro to normalize your data. This is a necessary first step into using the PivotTable suggested by rspahitz.
Sub Normalizer()
Dim rg As Range
Dim i As Long, j As Long, m As Long, n As Long, nn As Long
Application.ScreenUpdating = False
Set rg = Range("A2")    'Top left data cell in table. Note that you need header labels above this cell.
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp)).Resize(, 4) 'Four column table with all the data
n = rg.Rows.Count
For i = n To 1 Step -1
    m = rg.Cells(i, 3) - rg.Cells(i, 2)
    If m > 1 Then
        For j = 1 To m
            rg.Rows(i + 1).EntireRow.Insert
        Next
        rg.Rows(i).Resize(m + 1, 4).FillDown
        For j = 1 To m
            rg.Cells(i + j, 2) = rg.Cells(i, 2) + j
        Next
    End If
Next
Application.ScreenUpdating = True

End Sub

Open in new window


The sample workbook contains your original data plus a PivotTable that I formatted with Conditional formatting to give the red and green colors.
holiday-plannerQ26937769.xlsm
0
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

byundtMechanical EngineerCommented:
Sample workbook using Excel 2003 conditional formatting (I hope)
holiday-plannerQ26937769.xls
0
rspahitzCommented:
Ah, I see.  You have date ranges, not specific dates.
I don't think the pivot table works with ranges.

Instead I took a different approach (attached)
 holiday-planner.xls

The idea is to create a formula that determines if each calendar day is with the holiday date range (columns B and C).  If so, show a value of 1 (meaning 1 holiday) otherwise show 0 (meaning no holiday)

When applied to each holiday request, you get a series of rows that show 0 or 1 for every calendar day.

Next, I used subtotals to summarize each of the person's requests (assumed to be sorted) and show the sub for each day for each person.  This will then show 0 for no holiday and anything greater than 0 for holiday.  If it's 1 then there is exactly one holiday request; if more than 1 there's a conflict.

Finally, I collapse the details and show just subtotals then use Conditional formatting to show green if the value is 1 (and maybe shade the number so you don't see it) and show red if the value is > 1 (and shade the number so you don't see it.)

Note that since I did this in Excel 2010, some of the colors may not back-translate quite right so the colors you see may be slightly off.

--
If you need details on how to use any of the above, let me know, but the subtotalled results seem to match the example you gave.
0
benlintonAuthor Commented:
rspahitz - that looks almost perfect.  Thank you.  As I add data to the first table, how do I update the second?  I'm expecting to be putting several hundred items in and was hoping that it would populate and highlight clashes automatically.  Is that possible?
0
benlintonAuthor Commented:
byundt - again, thank you for your work.  As above, how do I go about recalculating?
0
byundtMechanical EngineerCommented:
When I checked the .xls sample file in Excel 2003, the conditional formatting didn't get formatted the way I wanted. Here is a new version:
holiday-plannerQ26937769.xls
0
rspahitzCommented:
benlinton, there are several ways to get it to auto-add those items as you type them, all of which need VBA (at least for the one's I'm thinking of)

1) Have a form that lets you add the new users, which will also add the new items to the other sheet.
2) Intercept when you change a cell and add to the other sheet.

However, at this point I recommend simply adding all of the users/employees then updating the other page to create subtotals for the new group of entries.  Unless you plan to do a lot more changes, the manual effort is probably better than a VBA solution that will take more time to develop and test and may not match all of your needs.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
To use the macro:
1) Make sure that you have header labels above each column in your data table
2) Run the macro using ALT + F8

The key steps in the wizard for the PivotTable are:
3) Select any cell in the normalized table.
4) Data...PivotTable or PivotChart
5) In the wizard, drag the Name field to the Row Items Go Here
6) Drag the Starting date to the Column Items Go Here
7) Drag the Name field to the Data Items Go Here
8) Apply Conditional Formatting to your counts
0
byundtMechanical EngineerCommented:
I suggest using a dynamic named range with the data. It will grow and contract automatically as you add data.
1) Open the Insert...Names...Define menu item

2) In the Refers to field, use a formula like:

=$A$1:INDEX($D$1:$D$10000,COUNTA($A$1:$A$10000))              make sure it includes the header labels!


You can then update the PivotTable by right-clicking and choosing to refresh.

You can use the Paintbrush icon to copy the Conditional Formatting over
0
byundtMechanical EngineerCommented:
I revised the macro so you can run it repeatedly on the same data. It will clear the End date when it has processed the data. This lets the macro detect new data from old.

I also set the PivotTable up using a dynamic named range.
holiday-plannerQ26937769.xls
0
benlintonAuthor Commented:
Thank you both very much indeed, I'm fairly sure that both solutions will provide me with what I need.  

I may come back to you when I get stuck, but wanted to accept your answers asap to show credit where it's due!

Thanks again.
0
rspahitzCommented:
Thanks!  click the link when you're ready for the next related question ("You can also ask a related question.")
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.