To remove an individual item from a list in Python you can use either a list method or the del statement, both approaches mutate the original list – meaning the list being operated on changes according to the instruction. To remove multiple items from a list you can use the del statement with the slice operator, or you can use a list comprehension with an if condition to filter the unnecessary items in your original list. Using the del statement will mutate the original list, whereas using a list comprehension will create a new list leaving the original untouched. ...

The VLOOKUP formula is a popular function for getting the value from a tabular data set and has 3 required parameters and an optional fourth, and looks something like this: = V L O O K U P ( r e f e r e n c e _ v a l u e , d a t a , r e t u r n i n g _ c o l u m n _ v a l u e , [ i s _ s o r t e d ] ) The first parameter reference_value is the value you are searching for in the first column of your data set (the second parameter). The third parameter returning_column_value is the column from your data set that you wish to return, and the optional fourth parameter is_sorted is a boolean type where if this is true (by default it is) then the returned value from this formula will be its first match, otherwise if FALSE then returns the closest match. ...

To sort a list of strings according to their length, use a lambda function on the key parameter with the lambda function using the len function on each element. This is represented as follows using the sorted function: sorted(my_list, key=lambda el: len(el)). Here is a simple example demonstrating the use of this code: 1 2 3 4 my_list = ['Smithers', 'Smiths', 'Sims', 'Smalls'] my_sorted_list = sorted(my_list, key=lambda el: len(el)) print(my_sorted_list) # ['Sims', 'Smiths', 'Smalls', 'Smithers'] Or, if using the .sort() list method would be represented as follows: ...

Can you use noncontiguous ranges in the data parameter in a QUERY() function? Yes, you can. Google Sheet’s QUERY() function permits the ability to use noncontiguous ranges, but they must be wrapped in set notation with curly braces {}. When applying a filter in the query parameter, you will need to use the ColX notation to reference the specific range according to its order in the set. Recall that the QUERY formula contains a total of three parameters as follows: ...

Merged cells are a great way to span content over multiple cells, and you can easily remove a single merged cell with one click by clicking on the merge cell button, but unfortunately, you cannot apply the same process when trying to unmerge a whole array of rows or columns that contain cells with multiple merges. For example, have a look at the following spreadsheet which contains a multiplicity of rows all containing 3 merged cells: ...

There will come a time when using Google Sheets where you will be checking the value of a cell against multiple criteria, which one is the best to use? There are three handy functions you can use within a single cell to handle multiple conditions: IF, IFS and SWITCH. Let’s examine each formula individually and how they might fit your needed case. IF Formula The IF formula is the simplest and probably one of the first formulas most users of spreadsheets start with. Its a formula that contains three parameters: ...

Recently I had a column of names in a spreadsheet with the following structure: Last Name, First Name and they needed to change to the structure: First Name Last Name. For example, the original structure of someone’s name would be Smith, John and this needed to change to John Smith. So a couple of medications were needed. First, fetch the respective names and identify them positionally according to their place on either side of the comma. Then, remove the comma and concatenate the names using a space character to separate them. ...

I recently had an exercise where I needed to extract all the text from an OmniGraffle document. The task at hand was to check a beautifully designed organisation chart against a spreadsheet list of staff. Doing this task manually was going to take far too long. There had to be a better way of extracting the information I needed from the document: and thankfully there was! OmniGraffle have a new feature labelled Omni-Automation for OmniGraffle that allows you to write JavaScript code and create/modify objects on the canvas. ...

To convert a string to an integer you use the handy int() function. For example, if you have a string such as "79" and you insert this like so into the int() function: int("79") you get the result 79. But what happens when you don’t have a string that easily converts into a number? Here are some examples where converting a string into an integer type simply will not work in Python (for obvious reasons): ...

One of the first formulas I started with when exploring more about the functionality of spreadsheets is the IF formula. The IF formula is easy to understand and contains only three parameters which are all required. The first parameter is the condition to check, the second is the returned value if the condition is true, and the third parameter is the returned value if the condition evaluates to false. = I F ( c o n d i t i o n , v a l u e _ i f _ t r u e , v a l u e _ i f _ f a l s e ) Let’s look at a simple example: ...