Vlookup formula vs. Index, Match ??
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
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.
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
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?
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Vlookup formula vs. Index, Match ??
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.
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.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Vlookup formula vs. Index, Match ??
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
is entirely correct, but it can be simplified to
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
Code: Select all
lngLastSC = lngStaffLC
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
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..........
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Vlookup formula vs. Index, Match ??
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.
=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.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
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
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Vlookup formula vs. Index, Match ??
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.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
OK..thanks...
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
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.....
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.....
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Vlookup formula vs. Index, Match ??
I fear it has become too complicated for me to understand.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
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.....
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Vlookup formula vs. Index, Match ??
Perhaps someone else will be willing to take a look at it. It would take me too much time.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
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
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
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
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:
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
Last edited by HansV on 04 Nov 2014, 06:14, edited 1 time in total.
Reason: to add [code] ... [/code] tags around code.
Reason: to add [code] ... [/code] tags around code.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Vlookup formula vs. Index, Match ??
Adding the line should work:
What is the problem with it?
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)
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
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 ?)......
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Vlookup formula vs. Index, Match ??
Are you sure that you've got the spelling correct? If you take the string "zzHouse" and replace "zz" with "", it should return "House":
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Vlookup formula vs. Index, Match ??
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
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
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Vlookup formula vs. Index, Match ??
I can't explain that, I'd have to see the workbook.
Best wishes,
Hans
Hans