Vlookup formula vs. Index, Match ??

[dmcnab]

Good morning loungers....I need some help with some code (and perhaps, even, suggestions re layout of a worksheet. I attach a sample. On the SCHEDULE wsheet, you can use a dropdown menu to select worksites for the staff shown across the top in row 14. On the LOCATION wsheet, I have manually entered the data that I am trying to 'copy over' from SCHEDULE....I need help with the formula that would work on LOCATION in transposing (??) or getting data across from SCHEDULE to show worksites and the staff at each worksite.

I'm sure that the way that I have laid out the LOCATION wsheet is rather cumbersome, but the matter is complicated by people being AM in one spot, and PM in another spot...or multiple people having the same schedule (eg; Vacation) or some being on Vacation and others overlapping with Vacation - A.M.

I'm not too concerned about having the color coding appear on the LOCATION sheet and as far as the asterisks or the carets etc are concerned, I am thinking that the data could carry over with the ^^ or the * etc, and I would use SUBSTITUTE to eliminate the * or the ^ etc etc...my immediate concern is getting a formula that will transfer data from SCHEDULE to LOCATION as shown.....thanks to anyone who can help with this.

[HansV]

See the attached version. I have modified the layout of the Location sheet slightly - there is only one row for each AM code and one for each PM code.

I don't know how to do this with manageable formulas, so I wrote a macro and assigned it to a command button on the Schedule sheet.

If there are multiple staff members with the same code, they are displayed on different lines within the same cell.
For whole-day codes, both the AM and PM row are populated.

Sample for new worksheet.xlsm

[dmcnab]

Thank you very much for this Hans...a huge improvement....I am returning the wbook, with some notes on the LOCATION wsheet showing some errors in copying over data from SCHEDULE to LOCATION...you will see that I have added a column etc (and hidden a column) in an effort to make it easier on the eyes....on Location, the correct cells are green and the errors are yellow......in each case, the error is a failure to copy over all data.

I don't really undertsand that code you wrote, altho I was able to adjust it to accommodate the newly-added column (in 'clear location cells', I changed d17:ah54 to e17:ai54).....is there a limit on the number of staff members? You show 14, but that number may vary, from time to time and office to office, and I can't be changing that part of the macro each time staff number changes.....is 14 an upper limit, and if so, could I stipulate, say, 50 to always have enough 'room' for changing staff numbers?

Also, I assume that I can adjust the lngSR from 15-45 to a different range (eg: 15-383) once I assemble the entire calendar? And in the 'loop thru staff', what is the meaning of lngSC 6 to 6 ?

[HansV]

How can we determine the last "staff" column on the Schedule sheet? I can't use row 14 for that, for you have some cell with text to the right of the last staff member. Moreover, next time you will probably move row 14 to row 15 or to row 37...

[dmcnab]

The text that is in row 14 (to the right of the last staff member) is for my use only...I can move it up to row 13...in the actual workbook, I can also move that text to row 13, and in the actual workbook the staff row is row 14 and there are 50 columns allocated to staff (F:BC)...not all 50 columns have a name in them, and then other material exists to the right of column BC, but the short answer is that there are 50 columns (F:BC) dedicated to staff names -- does that help ?.........and is the code you wrote another macro that I can insert into an existing module in the workbook, and then add a command button to run it?

[HansV]

But what if you decide to insert or delete rows, so that the staff names are in another row. How can we determine where the staff names are?

[dmcnab]

They are always in row 14.....the only variable is whether is have a name in each column from F:BC, or in fewer columns (eg: F:AA)...but on the Schedule sheet, the starting column is always F and the name row is always 14.....

[HansV]

OK, here is a new version. It determines how many rows and columns are in use on both the Schedule and Location sheets.
If you change the layout of one of these sheets, e.g. move the date column or the staff row, you have to review the values of the constants at the beginning of the macro.

You can copy the macro into a module in your own workbook, then create a command button from Forms Controls on the Schedule sheet and assign the FillLocation macro to it.

Sample for new worksheet from hans with macro button.xlsm

[dmcnab]

Good morning, Hans...I have been working with the last file/code you gave me and I have noticed a slight glitch. The intention would be that a user would enter site names (eg: 001, 002, Site 1 etc etc) on Location sheet, in col B..and that a formula in col C would create site names such as 001 - A.M., 001 - P.M., Site 1 - A.M., Site 1 - P.M. and so on....in previous attachments, the site names in col C were typed in full (ie: no formula).....everything worked fine.....however, if I use a formula in col C to 'expand' the site name to show " - A.M." and " - P.M.", the code won't work if there are 2 different people assigned to the same site (1 in the AM and another in the PM)....this type of split assignment only works if the AM and PM site names are 'hard typed' into col C.........eg: compare Jan 6, Site 1 to Jan 7, Site 2 to Jan 8, Site 3......there is no transfer of data from Schedule to Location for Jan 6 or Jan 8 (where the data in C17 & C18 and C21 & C22 is produced using a formula) but there is data transfer for Jan 7 (where the data in col C is hard-typed into C19 and C20)......is there a way to allow for the use of formula in col C rather than expect a user to type the info into col C (and risk a typing error, which would cause a data transfer error)?..perhaps some way of formatting col C ?.....o.wise, everything else seems to work splendidly -- it is a slow data transfer process (with 50 col and about 365 rows) but it works like a charm.....

[HansV]

Fortunately, the fix is easy: change the line

Code: Select all

                    Set rngFound = wshL.Range(wshL.Cells(lngDatR + 1, lngFullCodeC), wshL.Cells(lngLastLR, lngFullCodeC)).Find(What:=strVal, LookAt:=xlWhole)

to

Code: Select all

                    Set rngFound = wshL.Range(wshL.Cells(lngDatR + 1, lngFullCodeC), wshL.Cells(lngLastLR, lngFullCodeC)).Find(What:=strVal, LookAt:=xlWhole, LookIn:=xlValues)

See the attached version.

Sample for new worksheet from hans with macro button needs fix .xlsm

[dmcnab]

Hi Hans....I am looking at what you posted and working through it..so far, so good...I have a question: in trying to understand the code, I am puzzled by the opening lines:

(Sub FillLocation()
Const lngDatC = 2 ' column with dates (B) on Schedule sheet

......it suggests a reference to the 2nd column (which is the 'day' col) but I was wodering if it is supposed to be Const IngDatC = 3 (a reference to the 3rd column, which would be the 'date' column)

[HansV]

It doesn't really matter - there are dates both in column B and in column C. The constant lngDatC is only used to determine the last used row of the table on the Schedule sheet. We can use either column B or column C for that.

[dmcnab]

Good morning Hans.....thank you, again, for your help with this coding...so far it is great....I have spent some time trying to come up with some sort of conditional formatting that could possibly assist in cleaning up the appearance of the Location sheet......it is awfully busy looking, with the duplicate entries for AM and PM....I am attaching a modified version of the last workbook you posted...you will see that some cells are colored yellow (manually) to show days where the same person is in the same location in AM and PM.........on the Locations sheet, you will see that I have changed the code you wrote so that the transfer of data goes from Schedule to Location stating at row 40 (instead of row 17).....my plan would be to hide rows 39-57, and use formula in range E17:AI34...this would move the data up to the portion of the sheet that is visible......I was trying to make a cond formatting formula for E17:AI34 that would work like this: at the moment, all data is centered horizontally and vertically.....if, for example, the AM and PM entries differ (see K19 and K20), then the data transfers as shown and there is no change to the alignment within the cells....but if the PM entry is exactly the same as the AM entry (see J27 and J28) , then J28 would be 'blank', the horizontal alignment of J27 would be bottom and the dotted line between J27 and J28 would disappear......this way, it wouldn't look as cluttered and would 'appear that the staff was in the same place all day.....another way would be to auto-merge J27 and J28 and as seems to be the 'merging rule, the data from the upper cell dominates....I imagination that any code would be on the Location sheet?........this would also accomplish the same thing......what do you think of this approach? Is it even feasible?

[HansV]

Good afternoon,

I don't see any advantage in duplicating the table on the Location sheet, it only makes things more complicated.

I would avoid merging cells, it will make the sheet very difficult to maintain.

Setting the fill color with conditional formatting isn't difficult - see the attached version.

Sample for new worksheet from hans, needing code that will clean up Location sheet.xlsm

[dmcnab]

Good morning, Hans....I am not sure if this post is still active, or if I need to start a new, second post...I am assuming that I can continue with this one? I have a question about the code you gave me for FillLocation...my understanding of it is that when it runs, it 'matches' dates in column C with names in row 14 and copies data that appears at each intersection of row+column (on Schedule) into the corresponding spot in Location. I have modified this code re: starting rows and columns etc for my real workbook. I gather that when the data is 'searched', that one of the parameters is the last column in row 14 (ie: that the code looks for the last column in row 14 with something in it, and works backward from there). In my real workbook that worked fine until I decided to add more columns (with data) to the right of what is my last column with a name in it.....I added about 150 columns and there is data in all of these in row 14...as you can imagine, when I run the code now, it takes forever to do the global 'search+copy' from Schedule to Location............these new columns have nothing to do with the 'search+copy' exercise, and I am wondering how to limit the search+copy to only the relevant columns on the Schedule sheet (and ignore these newly added columns)? The relevant columns (row 14 showing names) are columns F to BC.

I suspect that I change something in this line: lngLastLC = wshL.Cells(lngDatR, wshL.Columns.Count).End(xlToLeft).Column (maybe change .End(xlToLeft).Column to specify 'columns F:BC', but I am not sure and I don't want to be guessing and ruin the entire code......can you tell me how to alter this code? Thanks......I am editing this post to now include the code that I am referring to:

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 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 = wshS.Cells(lngStaffR, wshS.Columns.Count).End(xlToLeft).Column
    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

End Sub

[Rudi]

Hi,

Please note that Hans is traveling currently and will not be available until approx. Saturday/Sunday this weekend.

[dmcnab]

Thank you, Rudi.....can you decipher my post..I 'addressed' it to Hans b/c he was the last person to reply, but if you can assist with this, I would be very appreciative...thanks.

[Rudi]

I was planning to look into it tonight when I'm back at home if no-one has contributed at that point.
Work is strangely keeping me quite occupied today

[dmcnab]

I know what you mean...thanks...will wait to hear from you or someone...bfn

[Rudi]

Here is the modified code based on your request.
I added a new constant: Const lngStaffLC = 55 ' last column with staff names on Schedule sheet
And updated this line: lngLastSC = wshS.Cells(lngStaffR, lngStaffLC).Column

The changes are untested...

Code: Select all

Sub FillLocation()
    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 = 39 ' date row on Location sheet
    Const lngCodeC = 2 ' column with sites on Location sheet
    Const lngFullCodeC = 3 ' column with sites+AM/PM on Location sheet
    Const lngAMC = 4 ' 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 = wshS.Cells(lngStaffR, lngStaffLC).Column
    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 = 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
End Sub