Home » Forums » Open Source Learning Center » Question and Answer

Multiple Match, Multiple Arrays, IFs, and Counts

Submitted by bekahmancino on 16 July, 2010 - 00:14

 

This is a schedule.

It comprises two parts: A listing with full job information, by job; and an overview of if or if not the engineers are being used, by engineer.

In the top part of the schedule, the engineer(s) assigned to the job is shown in column H (and I if two people are assigned).

The names in these columns are taken from the listing by engineer in the lower half of the schedule (to allow for a match).

'x' is placed in the appropriate week in the main schedule when this job is to take place.

 

The lower half of the schedule gives the value in the above cell, where the name at the left appears in column I. If there is no match in column I, it searches column H.

The problem (due to me requesting more than a simple TRUE/FALSE?) is that the same engineer might be assigned to two different jobs at two different times, and thus appear twice in column H and/or I.

My formula is only finding the first match.

(eg. U11 shows that 'Kurasinski, J' is assigned to a job, however it does not show in U51, as the formula stops calculating when it finds 'Kurasinski, J' in H4)

I did think about searching for 'x' and then trying to match the name ... but it seems that as long as I'm using an IF, I'm only going to get the first True or False.

 

I have not gone any further with this file since coming to this hurdle, but ultimately, this is the intended thought process for any point in the lower part of this schedule:

If there's an 'x' in the schedule above where >name on left< appears in column H or I, add 1 (if the engineer is double-booked, it will then show as 2 (+1+1) or 3 (+1+1+1).

 

I hope I've explained this sufficiently, and hope that I've not butchered the use of fomula too badly...

 

Many thanks!!

Rebekah.

AttachmentSize
channel.xls78.5 KB
‹ Pivot Table with Chart Extract Nth Word from a String ›
»
JPH's picture

VBa is the answer

Submitted by JPH on 28 July, 2010 - 15:29.

Hello bekahmancino, I'm a bit of an VBa freak so i tend to solve these things by adding my own function. Try this one and place it in a module:

  

Function sMatch(ByRef rRange As Range, ByRef strAssignedEngineer As String)
 
Dim tCount As Integer
 
Dim tCount2 As Integer
 
Dim lastCol As Integer
 
   tCount2 = 0
 
   lastCol = UBound(rRange.Value2, 2)
 
' First check column H

   For tCount = 1 To UBound(rRange.Value2, 1)
 
      If (rRange.Value2(tCount, 1) = strAssignedEngineer) And rRange.Value2(tCount, lastCol) <> "" Then
 
         tCount2 = tCount2 + 1
 
      End If
 
   Next tCount
 
' next check column I

   For tCount = 1 To UBound(rRange.Value2, 1)
 
      If (rRange.Value2(tCount, 2) = strAssignedEngineer) And rRange.Value2(tCount, lastCol) <> "" Then
 
         tCount2 = tCount2 + 1
 
      End If
 
   Next tCount
 
   sMatch = tCount2
 
End Function

 

Next In cell J49 place the formula: =sMatch($H$3:J$47;$I49)

This can be copied to al the other cells in your lower half from J49 to AM61

»

JPH, I REALLY appreciate your

Submitted by Anonymous on 28 July, 2010 - 15:50.

JPH,

I REALLY appreciate your assistance with this, and thoroughly enjoyed reading through the VBa!

I did wish to query the use of a semi-colon in the formula which you said to put into J49.

=sMatch($H$3:J$47;$I49)

Excel doesn't like the use of the semi-colon.

I replaced it with a comma, and it worked beautifully ... but I wanted to make sure that this was a correct replacement.

I got such a smile on my face seeing all those numbers pop up.

Thank you so very, very much!!!
This has genuinely made my day :)

Rebekah.

»
JPH's picture

Your welcome, thats'what we are here for

Submitted by JPH on 29 July, 2010 - 05:36.

Hello Rebekah,

Sry for the semicolon. I'm Dutch so i use the semicolon.

So I learned something today. Thank you. Next time I post something like this I'll remember to change the semicolons for commas.

Did you understand what the VBa does in this case ? Next time you'll try it yourselve, i'm sure.

Grtnx,

 

JPH

»