Vlookup formula vs. Index, Match ??

[dmcnab]

Hi Rudi...I have copied this into my real workbook and am testing it....the first time, it erased a bunch of things but that was caused by incorrect numbering of the constant rows and columns etc..I am correcting that and testing and will let you know how it goes. Thanks.

[dmcnab]

Good morning....Rudi - I have been working with the changes to Hans' code that you gave me and so far everything is working as it should.....Hans' code is amazing and you have helped me understand it a bit more.....I have a question: in the real workbook, the Location sheet (data is being moved from Schedule to Location) has about 365 columns and as many as 50 rows....that is a lot of checking&moving of data....is there some way to change the existing code so that is only moves data that has changed....I suspect that the long time it takes to copy data from Schedule to Location is because it needs to check for changes and then move all of the data....but if it only had to move data that changed (which might be 10% or 15% of all the data) could it be made to speed up?

[Rudi]

Hi,

Hans will be able to give a more accurate answer since he developed the code and has a better overall idea of the process you follow. I have not really been following this "project" so I don't know the details as well. Having just scanned the structure of your sheets it is obvious that the transfer of data is not just a simple copy/paste. The data needs to be transposed and it seems that multiple pieces of info needs to be added into single cells too....

Bottom line answer...I don't think that you'd be able to speed up the data transfer much more since it needs to be managed with multiple loops due to the complexity of your data structure. This is simply my initial opinion without knowing the details of your file.

[HansV]

I agree with Rudi. The code doesn't simply move text into a cell, it has to do a lot of checking because of the extraordinarily complicated way you structure your data. I think the slowness is a relatively modest price to pay for that.
You can apply all the changes you need to the Schedule sheet, then run the FillLocation macro once to repopulate the Location sheet, and get a cup of coffee while it's running

By the way, Rudi's line

Code: Select all

    lngLastSC = wshS.Cells(lngStaffR, lngStaffLC).Column

is entirely correct, but it can be simplified to

Code: Select all

    lngLastSC = lngStaffLC

[dmcnab]

Good morning....I am still working away at trying to improve on how data is 'transferred' from the Schedule sheet into the Location sheet......the huge amount of data that is 'moved' or transposed makes the 'Fill' operation very, very slow...the first time I ran it, the time was almost 20 mins (using about 370 columns and 200 rows)...eventually, I interrupted the code and it had a yellow error where the 'End If' lines are located....I imagine that I caused it to stop at that point....anyway, I am attaching a small sample of a workbook..it is the Location sheet and you will see that I have entered data b/w rows 228:281...in the real workbook this data would be there by virtue of formulas that bring it from the Schedule page, but I want to keep this sample small....in K9, I am working on an Index/Match formula, but it returns a #REF.....it should return 'Emp 5'...in previous posts, I was using asterisks and carets in the data (eg: ***444, or ^333, or ?222) -- I am still doing that on the Scheduling sheet, but the real-life formulas that copy the data from Scheduling to Location use a SUBSTITUTE formula to remove the asterisks, etc when the data lands on the Location sheet..............can you take a look at my attempt at an Index/Match formula (I borrowed it from another worksheet and am trying to modify it) and see what is wrong with it? I am hoping that this might be the right way to populate these cells. You will see that for the most part, the rooms are shown as 2, 2(am), 2(pm).....if it is possible to structure the room layout as I have done in rows 9:44, I would like to leave it like that, and hide column H -- however, I think that the formulas would be too complicated with data 'competing' with data for the same cell, and so I could structure it as shown in rows 45:47 and simply remove column H.....and if it the formulas are such that EMP 1 is in a room for the whole day and EMP 2 is in the same room for an (am) or (pm), that is perfectly fine if they overlap, b/c it may happen that way in reality anyway..........

[HansV]

You shouldn't use "General" as formatting code. You want text comparison here, so use "@". The formula in K9 should be

=IF(K$230="h","Closed",IF(ISERROR(MATCH($G9,TEXT(K$231:K$280,"@"),0)),"",INDEX($G$231:$G$280,MATCH($G9,TEXT(K$231:K$280,"@"),0))))

as an array formula, i.e. confirm the formula with Ctrl+Shift+Enter.

[dmcnab]

Thank you , Hans..it works well, but I think that I may nonetheless have to give up the ghost on this one....even if I format the sheet like rows 45:47 (2, 2(am), 2(pm)) and even if I could make it look less-busy, I now see that when I copy the formula all the way down, for example, VACATION shows EMP 4 (the first person assigned to VACATION on that day - Jan 2).....unless you know how to set it up so that multiple people can be assigned to the same location on the same day, I really can;t see creating things like Vacation1, Vacation2 etc etc...it will be too messy looking etc.....if you have suggestions, I would appreciate them, but if your best advice is to forget it, then please let me know..thanks

[HansV]

I don't think it is possible to use formulas to populate the Vacation rows. That would require VBA code because you'd have to loop through rows 231:280 to check which ones contain Vacation. Similar for Admin, Office, Course etc.

[dmcnab]

OK..thanks...

[dmcnab]

Hi Hans...I was wondering about this as a means of speeding up the transfer of data from Schedule to Location...see attached workbook (in the actual workbook, the Schedule sheet goes to row 381 and the Daily Notes and Weekly sheets cover an entire year (from Jan 1 to Jan 1 or 2 of the following year) but I have cut that out for size reasons)

Scheduling data is entered on Schedule and occasionally on Daily Notes....all of it finds its way onto Weekly.....on Location, I have added a number of columns that contain all of the variables for specified locations (rows 9:170) and in rows 171:190 I have 'other' types of assignments....the 'other' assignments shown in rows 171:190 are either full day or half-day, and none of them are combined with an asterisk or a question mark.

The existing code you gave me to "Fill Location" is still in the workbook as Module 3.......here is my question: if all of the variables (which are what makes this difficult) are shown on Location (columns G:T), could you use one of the Lookup formulas to populate Location rows 9:170, referring back to data entered in the Schedule page (or on the Weekly page if that were possible, b/c then it could read 'split' assignments such as that for Staff04 on Wed Jan 7th where it is 2(am) and 3(pm)......and you could still click the 'Fill Location' button to run your VBA code to complete the data transfer b/w Schedule and Location rows 171:190 ??...this would mean that your VBA code only runs on 20 rows instead of 190, theoretically making it faster ? What do you think of that approach? I have used some LOOKUP formulas on the Weekly page (see bottom), but don't know enough about complex Look-up formulas to devise one for Location rows 9:170 that takes into account the variations shown in columns G:T.....

[HansV]

I fear it has become too complicated for me to understand.

[dmcnab]

Sorry about that b/c I am not trying to complicate it....I guess my question is whether you can use LOOKUP formulas to transfer data from Schedule to Location (rows 9:170), and use your existing VBA code (that you call "Fill Location" and which is in the workbook that I attached) to transfer data from Schedule to Location (rows 171:190)......and if that is too complicated to assemble, I would not be surprised...just thought that I would float that possibility as a solution that might speed up the data transfer between Schedule and Location.....

[HansV]

Perhaps someone else will be willing to take a look at it. It would take me too much time.

[dmcnab]

That would be great of someone could do that.....to recap what I said earlier (for the benefit of someone new to the post), the issue is transferring data from Schedule sheet to the Location sheet...at the moment, this is done by Hans' code (which is in the workbook), which works great and does the job, but it is very slow in transferring the items -- the actual workbook covers an entire year and so the looping through takes considerable time.

My thinking was that for rows 9:170 on the Location sheet, the data could be transferred from Sched to Location using LOOKUP formulas (or INDEX & MATCH).....on the Location sheet, in columns G:T, I have shown all of the possible variations of assignments that one would match to a name using LOOKUPs or INDEX & MATCHs....and then I thought that we could adjust Han's code so that it only 'applies to' or runs for Location rows 171:190 (the items shown on those rows).......so, from a users perspective, most of the data would transfer automatically by virtue of a formula, and a user would also go to the Location page and call Hans' code to complete the data transfer for rows 171:190............the only reason I included the Weekly sheet in the workbook was b/c I didn't know whether it's easier to construct a LOOKUP formula using SCHED & LOCATION or using WEEKLY & LOCATION......hope this makes it clearer..??..??...thank you anyone who takes a look at this...and if it is too complex to do the transfer using formulas (given the variations in assignments shown in Locations G:T) then I accept that and will continue using Hans' existing code...thanks again

[dmcnab]

Good morning, Hans....can you tell me how to add StrVal to this code...you gave me the code as shown below (at the end of this post) and I have highlighted the part I am asking about....I already have the following:
strVal = Replace(strVal, "*", "")
strVal = Replace(strVal, "^", "")
strVal = Replace(strVal, "?", "")........and want to have strVal=Replace(strVal,"zz","") so that it will replace "zz" with ""................I tried to add it following your code, but it doesn't work and I am asking whether it doesn't work b/c it is letters, as opposed to 'symbols'....see full code below:

Code: Select all

Option Explicit
Sub Fill_Location()
    Const lngDatC = 2 ' column with dates (B) on Schedule sheet
    Const lngStaffR = 14 ' row with staff names on Schedule sheet
    Const lngStaffC = 6 ' first staff name column (F) on Schedule sheet
    Const lngStaffLC = 55 'last column with staff names on Schedule sheet
    Const lngDatR = 6 ' date row on Location sheet
    Const lngCodeC = 7 ' column with sites on Location sheet
    Const lngFullCodeC = 8 ' column with sites+AM/PM on Location sheet
    Const lngAMC = 9 ' column with AM/PM on Location sheet
    Dim wshS As Worksheet ' Schedule sheet
    Dim wshL As Worksheet ' Location sheet
    Dim lngSR As Long ' Schedule row
    Dim lngLastSR As Long
    Dim lngSC As Long ' Schedule column
    Dim lngLastSC As Long
    Dim lngLR As Long ' Location row
    Dim lngLastLR As Long
    Dim lngLC As Long ' Location column
    Dim lngLastLC As Long
    Dim rngFound As Range
    Dim rngLoc As Range
    Dim strVal As String
    Dim strStaff As String
    Application.ScreenUpdating = False
    Set wshS = Worksheets("Schedule")
    lngLastSR = wshS.Cells(wshS.Rows.Count, lngDatC).End(xlUp).row
    lngLastSC = lngStaffLC
    Set wshL = Worksheets("Location")
    lngLastLR = wshL.Cells(wshL.Rows.Count, lngAMC).End(xlUp).row
    lngLastLC = wshL.Cells(lngDatR, wshL.Columns.Count).End(xlToLeft).Column
    ' Clear location cells
    With wshL.Range(wshL.Cells(lngDatR + 1, lngAMC + 1), wshL.Cells(lngLastLR, lngLastLC))
        .ClearContents
        .Interior.ColorIndex = xlColorIndexNone
        .WrapText = True
    End With
    ' Loop through dates
    For lngSR = lngStaffR + 1 To lngLastSR
        lngLC = lngSR + lngAMC - lngStaffR
        ' Loop through staff
        For lngSC = lngStaffC To lngLastSC
            strStaff = wshS.Cells(lngStaffR, lngSC).Value
            strVal = wshS.Cells(lngSR, lngSC).Value
            strVal = Replace(strVal, "*", "")
            strVal = Replace(strVal, "^", "")
            strVal = Replace(strVal, "?", "")
            strVal = Trim(strVal)
            If strVal <> "" Then
                Set rngFound = wshL.Range(wshL.Cells(lngDatR + 1, lngCodeC), wshL.Cells(lngLastLR, lngCodeC)).Find(What:=strVal, LookAt:=xlWhole, LookIn:=xlValues)
                If rngFound Is Nothing Then
                    Set rngFound = wshL.Range(wshL.Cells(lngDatR + 1, lngFullCodeC), wshL.Cells(lngLastLR, lngFullCodeC)).Find(What:=strVal, LookAt:=xlWhole, LookIn:=xlValues)
                    If rngFound Is Nothing Then
                        ' Code not found - should not occur
                    Else
                        Set rngLoc = wshL.Cells(rngFound.row, lngLC)
                        If rngLoc.Value = "" Then
                            rngLoc.Value = strStaff
                        Else
                            rngLoc.Value = rngLoc.Value & vbLf & strStaff
                        End If
                    End If
                Else
                    Set rngLoc = wshL.Cells(rngFound.row, lngLC)
                    If rngLoc.Value = "" Then
                        rngLoc.Value = strStaff
                    Else
                        rngLoc.Value = rngLoc.Value & vbLf & strStaff
                    End If
                    Set rngLoc = rngLoc.Offset(1)
                    If rngLoc.Value = "" Then
                        rngLoc.Value = strStaff
                    Else
                        rngLoc.Value = rngLoc.Value & vbLf & strStaff
                    End If
                End If
            End If
        Next lngSC
    Next lngSR
    wshL.Select
    Application.ScreenUpdating = True
Call AF_location
End Sub

[HansV]

Adding the line should work:

Code: Select all

            strStaff = wshS.Cells(lngStaffR, lngSC).Value
            strVal = wshS.Cells(lngSR, lngSC).Value
            strVal = Replace(strVal, "*", "")
            strVal = Replace(strVal, "^", "")
            strVal = Replace(strVal, "?", "")
            ' *** New ***
            strVal = Replace(strVal, "zz", "")
            strVal = Trim(strVal)

What is the problem with it?

[dmcnab]

I added that code earlier, tried it and it didn't work, which is why I posted my question yesterday....if I have an expression such as ***house or ***123, it returns house or 123..if I have zzhouse or zz123 or zzHouse, it doesn't trim the zz from the front of the expression...it returns zzHouse etc......and that is why I asked if the 'trimming code' only worked on non-alpha characters (such as *, or ^ or ?)......

[HansV]

Are you sure that you've got the spelling correct? If you take the string "zzHouse" and replace "zz" with "", it should return "House":

S002.png

[dmcnab]

I would have thought so as well........when I ran it, I was expecting that zzRelief M-1 would have returned as Relief M-1.....but it didn't trim the zz, and so it came back as zzRelief M-1....here is that portion of the newest code:

For lngSC = lngStaffC To lngLastSC
strStaff = wshS.Cells(lngStaffR, lngSC).Value
strVal = wshS.Cells(lngSR, lngSC).Value
strVal = Replace(strVal, "*", "")
strVal = Replace(strVal, "^", "")
strVal = Replace(strVal, "?", "")
strVal = Replace(strVal, "zz", "")
strVal = Trim(strVal)
If strVal <> "" Then

[HansV]

I can't explain that, I'd have to see the workbook.