Tag: Ben Collins
In a data set I created, I have used it to add an ‘E’ and the word ‘Group’. This allows me to sort numerically, as well as display the text visually.
Your challenge is to create a single formula in cell B1 (shown in yellow below) that sorts this list alphabetically by the last name.
=SORT(A1:A10,INDEX(SPLIT(A1:A10," "),,2),1)
=ArrayFormula(QUERY({A1:A10,SPLIT(A1:A10," ")},"select Col1 order by Col3"))
=INDEX(SORT(REGEXEXTRACT(A1:A10,"((.*)( .*))"),3,1),,1)
=SORT(A1:A10,REGEXEXTRACT(A1:A10,"(?: )(\w*)"),1)
I love how a problem can have so many approaches.
One thing that I picked up is the ability to split a column and create an array on the fly. This is something that I really need to work on as too often I create hidden tabs which such fields and formulas.
You might be interested in Jon Dron and Terry Anderson’s book Teaching Crowds. I discussed it .
Google Sheets is great for quickly spinning up dashboards and analysis, but getting raw data into Sheets from databases can be tedious.
In this post we cover a few ways to get data from your SQL database into Google Sheets.
I help organizations make the switch from Microsoft Excel to Google Sheets through workshops, webinars and online courses.
I’m a Google Developer Expert specializing in Google Sheets. I’ve taught spreadsheet workshops and courses for the past four years.
There are a lot of myths surrounding Goog…
By far the biggest challenge of my life continues to be the balance of being a good husband and father whilst running my own business and keeping fit. It’s hard to not feel like you’re failing on all these fronts, despite going full tilt all the time. All one can do is keep trying!
This reminds me of a
about the changes moving from one child to two.I recently revisited a timetable solution that I developed to support schools with creating a basic timetable based on core data of:
- Days
- Sessions
- Forms
- Classes.
One of the problems that was uncovered was that the system we were trying to feed the information into would not accept staff codes for staff who were not yet active in the system. My initial answer was to find each of these staff members in the final output and delete their codes. The question that was raised was whether this could occur at the beginning of the process by leaving the codes associated with inactive staff blank in the list.
This created a problem where the blank cells were not being recognised and were subsequently not being generated in the output. I found if I added a blank space then my original formulas would work. I therefore added an IF formula to replace any blank cells with a blank character CHAR(32). This meant I replaced:
JOIN(",",'Basic Classes'!$C3:C103)
With:
ARRAYFORMULA(JOIN(",",(IF(ISBLANK('Basic Classes'!$C3:C103),CHAR(32),('Basic Classes'!$C3:C103))))
The issue with this though is that there would never be 100 classes. However, I wanted to create a flexible solution, therefore put in a range that would suffice all contexts. By replacing blanks with CHAR(32)
, this meant every timetable created had 100 teachers. This would not work.
To work around this, I created a series of dynamic ranges using your example of named ranges. I entered this formula in a cell and referenced it to create a named range. To get the right range I used the form list (Column B) as a reference as this is a required cell in my data collection:
"'Basic Classes'!$C3:C"&COUNTUNIQUE('Basic Classes'!$B$3:B)+2
The final formula was:
ARRAYFORMULA(JOIN(",",(IF(ISBLANK(INDIRECT(TEACHERS)),CHAR(32),INDIRECT(TEACHERS)))))
This meant that the blank cells were only replaced for classes without teachers.
Here is a copy of the spreadsheet if you are interested.