Solved: Create Matrix

[koala]

Hi Forum,

I am wishing to automate a leave matrix. I have attached a spreadsheet with dummy data.

On one sheet is raw data. The are two sections of data, one for normal leave and one for special leave.

What I would like to have is a macro that will look at the data, and then give a value of "l" for normal and "n" for special (the matrix is formatted with Windings font so it gives a "dot" for normal and a "box" for special.

This value goes into the "Leave Matrix" sheet for the corresponding employee and date. The dates are inclusive.

There could be any number of rows of data in the raw data sheet

I have shown the first result for each leave type on the "Leave Matrix" sheet.

I am using excel 2002

Any help is greatly appreciated.

Thanks in advance

Koala

[koala]

Whoops, Sorry I uploaded the earlier version of the file.

This file has the first result shown on the matrix page

cheers
Koala

[Bob Phillips]

You could so it with all formulae

=IF(SUMPRODUCT(--(I$1='Raw Data'!$E$2:$E$200),--($A2>='Raw Data'!$F$2:$F$200),--($A2<='Raw Data'!$G$2:$G$200))>=1,"n",
IF(SUMPRODUCT(--(I$1='Raw Data'!$A$2:$A$200),--($A2>='Raw Data'!$B$2:$B$200),--($A2<='Raw Data'!$C$2:$C$200))>=1,"l",""))

This might get a bit slow, so here is a VBA version as well



Sub ProcessData()
Dim shLeave As Worksheet

Application.ScreenUpdating = False

Set shLeave = Worksheets("Leave Matrix")

Call SetLeaveType(shLeave, Worksheets("Raw Data").Columns("A"), "l")
Call SetLeaveType(shLeave, Worksheets("Raw Data").Columns("E"), "n")

Application.ScreenUpdating = True
End Sub

Private Function SetLeaveType(sh As Worksheet, LeaveType As Range, LeaveId As String)
Dim LastRow As Long
Dim FindCol As Long
Dim StartRow As Long
Dim EndRow As Long
Dim i As Long, j As Long

With Worksheets("Raw Data")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow

FindCol = 0
StartRow = 0
EndRow = 0
On Error Resume Next
FindCol = Application.Match(.Cells(i, LeaveType.Column), sh.Rows(1), 0)
If FindCol > 0 Then

StartRow = Application.Match(.Cells(i, LeaveType.Column + 1), sh.Columns("A"), 0)
EndRow = Application.Match(.Cells(i, LeaveType.Column + 2), sh.Columns("A"), 0)
If StartRow > 0 And EndRow > 0 Then

sh.Cells(StartRow, FindCol).Resize(EndRow - StartRow + 1).Value = LeaveId
End If
End If
On Error GoTo 0
Next i
End With
End Function

[koala]

Thanks XLD,

The VBA works perfect. I have not tried the formulae yet, but will do so in a few moments.

I have learnt quite a lot recently from reading the posts and responses in this forum, and always spend a lot of time looking at the code to try to get a clear understanding of how/why it works, and your code is always great.

There are two things in this one that I dont understand though, so if you have time maybe you can help me to learn from it.

The first is you declared both i and j as long, and i dont know why j is there.

The second is the "With" statement looks at column A, but not at column E (which is where I thought the j might come in.).

I realise that I am imposing on your time, so if you dont have time to reply, that is ok, you have already assisted me greatly.

Many Thanks
Koala

[Bob Phillips]

You have caught a couple of glitches in my code :)



The first is you declared both i and j as long, and i dont know why j is there.

I originally thought of looping through the first sheet, down rows and across columns. I soon realised it would be far more efficient the way that I have done it. The j was to loop the columns in my first idea, so is superfluous and can be removed.


The second is the "With" statement looks at column A, but not at column E (which is where I thought the j might come in.).

I realise that I am imposing on your time, so if you dont have time to reply, that is ok, you have already assisted me greatly.

Originally, I was going to re-code the leave and special leave loops as separate code, but decided to put it in a generic function. Again that is a throw-back to my original code. It is no problem, the second leave type will just process more rows than needed, but would be if you have more rows of the second leave type. To correct it, change


LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row



to


LastRow = .Cells(.Rows.Count, LeaveType.Column).End(xlUp).Row

[koala]

Thanks XLD,

I understand it now.

As always you have been a great help. :bow: :bow:

Cheers
Koala