Is it best practice to add helper columns or is there a better way to vlookup?
I need to lookup pay rate exceptions from a separate sheet. On the separate sheet, I have four tables:
1. Client names, days of the week, and job type
2. Second table with days of the week, job type and location
3. Third table with Days of the week and job type
4. Fourth table for Standard rates including days of the week and job type
How I have done this is a macro creates three new tables, creating combinations of:
1. Combo 1: Client + Days of the week + Job Type
2. Combo 2: Days of the week + Job Type + Location
3. Combo 3: Days of the week + job type
I have also added these combo columns on the separate sheet, and am using a vlookup with nested if to check in hierarchy of Table 1, then 2, then 3, and if it matches none of the exceptions then table 4 Standard rates.
The result is a very long if statement with many vlookups.
It works fine but I want to make sure I'm following best practices and I'm not sure if there is a better way to do this. I tried to use AND statements but it ended up looking even more complicated. So I'm just looking for advice to make sure I'm learning the right way.
Re: Is it best practice to add helper columns or is there a better way to vlookup?
Here is an example file.
In my original case the Exception sheet is a separate file.
So I would have the Main file and a macro that reads the values from the second and creates the third sheet. Here three helper columns are added and then used for vlookups.
I'm trying to understand if this is best practice or if there is a better way to do this.
Thanks!
Re: Is it best practice to add helper columns or is there a better way to vlookup?
Hello.
In the 'Exceptions' sheet you have 4 tables from which -I imagine- you obtain a 'Pay' value and put that value in column G of the 'Main file' sheet.
To help you, you must explain when one table or another is used within those 4 mentioned tables.
Attached cleaner workbook.
You are always very welcome if you add reputation by clicking the * (bottom left) of each message that has helped you.
Re: Is it best practice to add helper columns or is there a better way to vlookup?
Hi apologies for not adding more detail,
So, the hierarchy goes, it first sees if any of the client exception applies to the invoices, in case none of those special rates apply then it checks if any location exception applies as there are certain locations that pay differently for different roles based on the day, it then checks for any role exceptions as certain roles are paid differently, neither location or role depends on the client for the time being, and finally if none of those apply, it looks at the standard rates and applies those. I would consider combining all four tables into one, but then it might be difficult for the non-technical folks updating the pay rates to keep track of how to plug in the values.
Re: Is it best practice to add helper columns or is there a better way to vlookup?
Ok: Let's get started on this!
- On the 'Exceptions' sheet are the 4 tables. Later we will look for this data in the second workbook.
Now what matters is that -it seems to me- that your tables are incomplete.
- In the 'Main File' sheet you will see that I removed one of the helper columns and changed the formula in column C.
- You can 'Click' the red button and analyze the results: when none of the 4 keys are found, the cell remains empty.
Re: Is it best practice to add helper columns or is there a better way to vlookup?
I went through your Macro and I'm new to this so I apologize it was a bit complex for me understand. The tables aren't incomplete, as if it meets none of the rates, there is a standard rate of 10 set. Attaching a file to show you how my macro looks, it is a bit basic as I am still learning.
The Final Sheet that exists, my plan was to just have it and save it as a separate file so that the helper columns don't affect it.
Re: Is it best practice to add helper columns or is there a better way to vlookup?
Frankly, I don't understand why you re-upload your workbook to the Forum when -it's visible- all but one of its results are wrong.
For my part, I show you the latest version of the development adding what you had omitted to mention: the value 10 for the cases that are not included in any of the 4 tables.
To do this, I added a new and small table in which you can write the corresponding value (for example: 10).
And please understand: there is no point in creating all those helper columns. They are not needed!
Re: Is it best practice to add helper columns or is there a better way to vlookup?
Hi,
Sorry I uploaded the second copy with a macro so you could see the logic I was working with. As you can see from the Macro code, I'm new to this and am having a hard time understanding your macro so I can finetune it to my need. If possible could you maybe add a couple of comments to direct which part of your macro is doing what so I can follow along.
Thank you.
Re: Is it best practice to add helper columns or is there a better way to vlookup?
Attached comments:
PHP Code:
Sub Macro8() Dim D, a, Q&, i&, iKey, j% Application.ScreenUpdating = False Set D = CreateObject("Scripting.Dictionary") Rem -------------------\ Rem The lookup keys from tables 1 and 2 are added to the dictionary: For j = 1 To 2 a = Sheets("Exceptions").ListObjects(j).DataBodyRange: Q = UBound(a) For i = 1 To Q iKey = "|" & a(i, 1) & "|" & a(i, 2) & "|" & a(i, 3) D.Add iKey, a(i, 4) Next Next Rem -------------------\ Rem The lookup keys from tables 3 and 4 are added to the dictionary: For j = 3 To 4 a = Sheets("Exceptions").ListObjects(j).DataBodyRange: Q = UBound(a) For i = 1 To Q iKey = "|" & a(i, 1) & "|" & a(i, 2) D.Add iKey, a(i, 3) Next Next Rem -------------------\ Rem The three types of keys are assembled and looked up in the dictionary: With Sheets("Main file").ListObjects(1).DataBodyRange a = .Cells: Q = UBound(a): ReDim iKey(1 To 3) For i = 1 To Q a(i, 6) = Empty iKey(1) = "|" & a(i, 1) & "|" & a(i, 3) & "|" & a(i, 4) iKey(2) = "|" & a(i, 5) & "|" & a(i, 3) & "|" & a(i, 4) iKey(3) = "|" & a(i, 3) & "|" & a(i, 4) Select Case True Case D.Exists(iKey(1)): a(i, 6) = D(iKey(1)) Case D.Exists(iKey(2)): a(i, 6) = D(iKey(2)) Case D.Exists(iKey(3)): a(i, 6) = D(iKey(3)) End Select Rem The final value is defined: the data found in the dictionary or the standard value: a(i, 1) = IIf(a(i, 6) = Empty, Range("St_Rate"), a(i, 6)) Next .Columns(6) = a End With MsgBox "The End." End Sub
Last edited by beyond Excel; 02-16-2023 at 08:53 AM.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks