BizTrix

If you’re wondering how to get the address from the TRIMRANGE result shown in the previous post (link below), then here are two options – the “long” way and the “short” way. ? The “long” way: Because TRIMRANGE returns a range reference, you can get the top-left and bottom-right cells using TAKE. You can then obtain the location using ADDRESS. (CELL could also be used in this scenario). LET will help simplify this greatly. If you’re using a trim reference (.:. | .: | :.) then you don’t need the TRIMRANGE function (e.g. TAKE(B2.:.F6,1,1)): ?? Start with TRIMRANGE ?? TRIMRANGE(B2:F6,3,3) ?? Add TAKE (top-left cell) ?? TAKE(TRIMRANGE(B2:F6,3,3), 1, 1) ?? Add LET ?? LET(tr, TAKE(TRIMRANGE(B2:F6,3,3), 1, 1), tr) ?? Add ADDRESS ?? LET(tr, TAKE(TRIMRANGE(B2:F6,3,3), 1, 1), ADDRESS(ROW(tr), COLUMN(tr), 4)) This pattern can be used to take the bottom-right cell using -1. As shown in the image, you can pass an array to the parameters of TAKE to avoid having to repeat the function. Wrapping this in TEXTJOIN with a colon (:) delimiter will finish it off: ?? =TEXTJOIN(":", FALSE, [insert let formula from above]) ? The “short” way: The Immediate Window in the Visual Basic Editor (VBE) will let you easily access the Address property of a Range object: ?? Open VBE (Alt+F11, or Excel: Developer | Visual Basic) ?? Open the Immediate Window (Ctrl+G, or VBE: View | Immediate Window) ? You can “ask questions” directly in the Immediate Window by placing a question mark (?) in front of your statement. When you are finished with the statement, press Enter: ?? ?Range("B2.:.F6").Address(False, False) This trim reference example lets us use the trim reference directly in the Range object. What if you need an option that doesn’t have a trim reference? ?? Writing TRIMRANGE directly returns an error ?? ?TRIMRANGE(B2:F6,3,2) ?? Accessing the WorksheetFunction class reveals that TRIMRANGE doesn’t exist ?? ?WorksheetFunction.tr ?? Instead, use Evaluate to resolve this string input to a Range object ?? ?Evaluate("TRIMRANGE(B2:F6,3,2)").Address(False, False) I often use these techniques when I need to explore how Excel is handling the Range. ? Let me know in the comments if you found these techniques useful. BizTrix trains on business skills: https://biztrix.us/ Previous Post: https://lnkd.in/ekyD9YKq?

TRIMRANGE showed up in my Excel this week. I wrote about this function when it was first announced (link below), and now I can test it out. I won’t rehash what was previously stated in that post. Rather, I’ll showcase the new reference operator that accompanies TRIMRANGE. The colon operator (:) is the most prevalent reference operator. Adding a dot to the left, right, or left and right of the colon will allow you to mimic three of the TRIMRANGE options: 1?? =TRIMRANGE(B2:F6,3,3) ?? =B2.:.F6 ?? C3:E5 2?? =TRIMRANGE(B2:F6,2,2) ?? =B2:.F6 ?? B2:E5 3?? =TRIMRANGE(B2:F6,1,1) ?? =B2.:F6 ?? C3:F6 This dot-colon notation (.:. | :. | .:) is known as a trim reference. If you need a different combination for the row-column trim such as =TRIMRANGE(B2:F6,3,2) ?? B3:E5, then you won’t be able to use the trim reference to accomplish this. A use case that can be added to the previous post for this function’s application is in reducing the number of cells that are passed into a formula, such as for entire column selections. This may reduce calculation burdens (i.e. avoiding the status bar message of “Calculation (x Threads): y%”). Be careful when selecting entire rows or columns in isolation versus as a group: 4?? =B.:.B ?? #REF 4?? =C.:.C ?? C4 4?? =D.:.D ?? D3:D5 4?? =E.:.E ?? E4 5?? =B:.F ?? B1:E5 6?? =B.:F ?? C3:F1048576 Also be cautious when applying the trim to columns, for example, because if you do something like =B.:F in this example then you aren’t really achieving your intended outcome to improve those calculation times. You may also create #SPILL! errors depending on the location of your formula and the trim range option. Let me know in the comments if you have used TRIMRANGE. Previous Post: https://lnkd.in/ek_DWadp BizTrix trains on business skills: https://biztrix.us/

Have you ever wondered why you see letters underlined on a dialog box? As an example, the Format Cells dialog box in Excel has many underlined letters. The image below shows a small cross section of underlined letters on the Alignment tab: ?? “H” in horizontal ?? “V” in vertical ?? “W” in wrap text ?? etc. When I was first learning Excel, I didn’t know why letters were underlined on dialog boxes. It even seemed to me like the underlines were random. I never stopped to think, “Hey, maybe there is a reason for this.” It wasn’t until I started coding in VBA that I learned the purpose of these underlined letters, which are called “accelerator keys.” These accelerator keys make it so you can “jump” to that control using Alt+Letter rather than tabbing to the control or clicking the control with the mouse. For example, you can check the “Wrap text” check box with Alt+W. Let’s look to VBA to help with this concept. The User Form (which I created in VBA) shows that there are two controls: ?? Label1 (which has a caption of “Name”) ?? TextBox1 Notice that Label1 has an “Accelerator” property. The property is set to “N” and thereby the “N” in “Name” is underlined. TextBox1 does not have an “Accelerator” property (because it inherently has no text like the label does). If Alt+W checks the check box, then what does Alt+N do in the User Form? Alt+N moves focus to the text box and puts a blinking cursor inside the text box. This is because the “TabIndex” property has TextBox1 after Label1 (so Label1's accelerator key will move to the next tab index): ?? Label1.TabIndex = 0 ?? TextBox1.TabIndex = 1 Accelerator keys are a way software developers help boost the productivity of its users ?? I’m grateful this concept was implemented because I use it every day. I’m also grateful I took the step (many years ago) to learn VBA because it helped me become far better at Excel than I could have imagined. Let me know in the comments if you use accelerator keys. P.S. There’s much more I could say about this topic (including some nuances), but this is a good starting point. BizTrix trains on business skills: https://biztrix.us/

A colleague frantically phoned me this week and said, “The CFO is asking why our weekly report is late. I should have sent it yesterday, and I can’t get the query to run. Will you please help me?” I gladly stepped in, and we connected over a video call. Me: “What seems to be the problem.” “Well, I tried running the query yesterday. After running for over 2 hours, I stopped the query. I thought, ‘Okay, I’ll try tonight during dinner time.’ I started it when I got home from work, and it was still running after 4 hours when I went to bed. Again, I stopped the query. I started the query again this morning and as you can see, it’s still running after 1.5 hours.” Me: “Let’s take a look at the query.” I’m not going to comment on the poor state the query was in. It needed some serious help. I said, “I don’t want to refactor the query because we won’t have time to validate the refactor since the CFO needed this yesterday; however, we’ll just look at a few optimization areas and see if that gets us anywhere.” Usually when I encounter these long run time situations, I’ll first look at the join and where clauses. In this case, a very simple change in the where clause returned the result of the query in less than 3 minutes! If you can spot the problem in the image below, then let me know in the comments. Side Note: Part of my SQL course (site link below) focuses on optimization and directly addresses this fundamental concept. BizTrix trains on business skills: https://biztrix.us/

Assuming you could use 4 functions only in Excel, which ones would you choose? This is a question a colleague asked me this week. (Thanks Samuel Gilman for the thought-provoking question). He specifically asked me to force myself to 4. As I reflected on this question throughout the week, I found myself struggling to settle on 4. I swapped my list daily ??. Here’s where I settled: ?? SUMPRODUCT ?? IF ?? SUBTOTAL ?? EOMONTH ?? SUMPRODUCT: This function does what SUMIFS does, and it replicates much of the lookup and positioning functions such as XLOOKUP and INDEX/(X)MATCH. So, I picked SUMPRODUCT to free up slots among my 4. (You can replicate the example below with SUM, but if my memory is correct, this capability for SUM was rolled out only a few years ago). ?? IF: Conditional logic, especially when transformed into binary flags, is extremely powerful. I frequently use flags. ?? SUBTOTAL: This function opens the option to not only perform various subtotaling functions, but it also adds extra flexibility to flag hidden data. (Could I get more out of AGGREGATE)? ?? EOMONTH: This is a function that helps me conveniently common size dates because most of what I do is time bound (so I have a need to use it a lot). What are some functions I debated heavily? OFFSET and TEXT. Let me know in the comments what your top 4 Excel functions are. (If you’re feeling ambitious, include your ‘why’ for 1 or more). BizTrix trains on business skills: https://biztrix.us/

I showcased an 'odd' use case of Excel’s End mode in a previous post. Though 'odd,' it illustrates an important behavior. When a range of cells is selected and then extended (with End and Extend Selection modes), it’s important to note the position of the active cell and the data that is in the direction of the movement. So, can you correctly answer what range is selected when you press Ctrl+Shift+Left Arrow in each of the four images? BizTrix trains on business skills: https://biztrix.us/ Previous Post: https://lnkd.in/e_2X3JDF

As you know, Excel’s End mode makes it very convenient to navigate around the spreadsheet. You can activate End mode by pressing the End key, which is noted in the status bar below the left-most sheet name. You can then press the Arrow Keys to get your desired navigation. Combine this with the Shift key and you can select cells while you navigate. Activating End mode can be tedious, so the Ctrl key provides the background activation for you. (The Shift key is the background activation for which mode? Also, do you know the shortcut key for this mode)? End mode behavior is largely straightforward; however, I randomly did something with End mode this week with VBA that caught me off guard. (Though the image below isn't the exact scenario that made me do a double take, it's enough to kickstart it). What cell reference is selected when you press Ctrl+Shift+Left Arrow in the image below? Let me know in the comments below. One of the things I like about Excel is how easy it is to create small test cases in order to understand how features behave. BizTrix trains on business skills: https://biztrix.us/

Let’s create those 6-digit Canada postcodes described previously (link below). As a recap, you need to generate all possible local delivery units (LDU) for each forward sortation area (FSA). The postcode is an alternating letter (L) and number (N) value: L-N-L-N-L-N (e.g. K1A0B1). Your SQL engine will dictate whether you can run the code in the images below. For example, SQL Server doesn’t use ‘create local temp table’ and it doesn’t have the ‘explode’ or ‘array’ functions. So, you may need to modify the syntax to fit your platform. ** If you’re curious as to how you can create this example in SQL Server then let me know. I’ll also throw in a recursive CTE for fun. Let me know in the comments. To shorten the example, you’ll use 3 letters and 3 numbers only. Additionally, you’ll place the results into the ‘postcode_combinations’ temporary table: ?? Create a table with the letter digits ?? Create a table with the number digits ?? Cross join the letter and number tables to create the N-L-N combinations. (The double pipe character || performs concatenation in this SQL engine) ?? Some sample results are as follows: 0A0 and 0A1 Next, you’ll take the postcode_combinations table, combine it with the pdf results, and feed the data into your delivery_area_surcharge (DAS) production table. (The image illustrates this as another temporary table to simplify the example). Some sample results are as follows: ?? US, 97493, 97493, $4.00, USD ?? CA, B0L, B0L0A0, $7.00, CAD Now that you have common sized the postcode across the US and CA, it will be simple for you to join the DAS table to the shipments table on the postcode. This is especially useful because you won’t need to recreate the DAS table every time a new shipment leaves your warehouse (and you won’t have a messy and inefficient join as seen previously ??). Let me know in the comments if you felt this example was helpful. Previous Post: https://lnkd.in/eDEEnS_z

Here’s a real-world example of needing a cross join. I’ll pose the question here and provide my solution in a separate post. (I will simplify the exact use case to avoid needing to dig into too many steps). As a company, you ship to the United States (US) and Canada (CA). Your shipping provider (e.g. FedEx, UPS, etc.) charges a delivery area surcharge (DAS) for locations that are ‘remote’ or ‘difficult to access.’ This fee is assessed based on specific postcodes: ?? In the US, the postcode is a 5-digit “zip code” comprised of number digits only (e.g. 06489) ?? In CA, the postcode is a 6-digit code comprised of both character and number digits (e.g. K1A0B1) It’s not necessary to understand all the components of a CA postcode (e.g. it should have a space after the first 3 digits), so I’ll leave out the nuances and focus on the pattern: alternating letter (L) and number (N) digits. ?? L-N-L-N-L-N (e.g. K1A0B1) Valid letters are A to Z and valid numbers are 0 to 9. (Again, I’m simplifying this because letters like D, F, Q are not used). ?? The first 3 digits (i.e. the left 3) represent the forward sortation area (FSA) ?? The last 3 digits (i.e. the right 3) represent the local delivery unit (LDU) The shipping provider sends you two PDF lists for DAS -- one for the US and one for CA. (I won’t focus on the steps for extracting the PDF data to the database. I used Python and SQL for this transformation). Here’s a sample of the US file (country code, zip code, surcharge, currency): ?? US, 97493, $4.00, USD ?? US, 07844, $2.10, USD Here’s a sample of the CA file (country code, FSA, surcharge, currency): ?? CA, A0A, $13.50, CAD ?? CA, B0L, $7.00, CAD You need to create a single database table that stores both the US and CA postcodes with the corresponding DAS. On the surface, you could combine the US 5-digit postcode with the CA 3-digit FSA into one table (i.e. union the samples above); however, this creates a separate problem. Customer addresses are stored at the lowest level -- 5 digits for the US and 6 digits for CA. You won’t be able to directly join a 6-digit CA postcode to a 3-digit FSA. This would require some ‘messy’ (and inefficient) SQL that may be difficult to manage if other countries are added in the future. Assume table ‘s’ has the shipping information for the package’s destination and table ‘das’ has the DAS information. You want to avoid the following: ?? select das.surcharge … ?? … on case when s.country = 'CA' then left(s.postcode, 3) else s.postcode end = das.postcode Combining these data sets into a single table requires you to common size both postcodes. This means that you need to generate the full 6-digit postcode for CA even though you have the FSA only. Using a SQL cross join, how can you create your DAS table such that your join condition can be as follows instead? ?? select das.surcharge … ?? … on s.postcode = das.postcode Hint: Each FSA should have 2,600 records BizTrix trains on business skills: https://biztrix.us/

There is another SQL join I have yet to address -- cross join. A cross join is a Cartesian product, meaning that each item in TableA is joined to each item in TableB. Thus, a join condition (i.e. the ‘on’ keyword) is unnecessary in a cross join.?(You can cross join as many tables as you want). ? A cross join is easy to conceptualize with small tables. Using the image below, the ‘sizes’ table is cross joined to the ‘ages’ table. Using the “Small” size only, the results are as follows:? ?? Small to 10? ?? Small to 11? ?? Small to 12? ? The size of the result set is the number of rows in ‘sizes’ (m) multiplied by the number of rows in ‘ages’ (n), or m * n. In this example, ‘sizes’ has 3 rows and ‘ages’ has 3 rows, so the result set is 9 rows (3 * 3).? ? Here are two very important notes about a cross join (without getting too deep in the details):? ?? It works well when the intended outcome is truly a Cartesian product (e.g. you need the combination among the tables)? ?? It can impact the performance of the query substantially (i.e. it can require a significant number of resources to run the query, and the result set can be extremely large)? ? Image you have three tables: A with 1 million rows, B with 10 million rows, and C with 100 million rows. How many rows are returned from a cross join among all three tables? 10^21 rows ?? It’s likely that your database administrator (DBA) will kill this query. Thus, if you think you need a cross join, then it’s a good idea to check with your DBA to determine if it’s truly what you need. There are legitimate use cases for a cross join (and I’ll discuss one in a separate post), but a cross join should be your last resort. (Personally, I’ve used a cross join less than 10 times over all the years I've been writing SQL). ? Like “join” being equivalent to “inner join,” you may see “cross join” replaced by a “,”. For example, ?? "from sizes s cross join ages a” becomes ?? “from sizes s, ages a” ? Let me know in the comments if you’ve run into problems running a cross join.? ? BizTrix trains on business skills: https://biztrix.us/