There are many factors to consider when creating a solution in Excel. Are we building the solution for someone else with basic Excel skills? Should we use a complex formula in only one column to save space or use helper formulas to simplify the solution? Let’s take a look at this question from Mike Girvin’s ExcelIsFun YouTube channel.
What’s The Requirement?
We start with this awkward data-set.
We need to count the number of ‘Sub Items’ per ‘Item’ (K123 has 4, H322 has 2, C983 has 6). We also need to lookup each ‘Item’ and return a text.
The ‘Description’ field should contain our formula solution. It shows the count of ‘Sub Items’ per ‘Item’ and also retrieves the ‘Item’ flower name.
Can We Use The ‘Fill In The Blanks’ Trick?
If so this would make it easier but in this case let’s assume that we can’t change the layout of fields ‘Item’ and ‘Sub-item’.
A Question That Can Make A BIG Difference!
Do we solve this using 1 formula or use helper columns?
If we try to use only 1 formula then it could become a very complex solution. What if you or someone else needs to slightly modify the formula a few weeks from now? How long would it take to audit the formula? If you build the solution for someone else wouldn’t you want them to understand the solution whenever possible? If not they could be stopping by your desk a lot!
Single Formula Approach Examples
Example #1
=IF(A6=””,IF(COUNTIFS(A6:$A$20,”<>”)=0,ROWS($A$6:$A$20)-MATCH(“zzzzz”,$A$6:$A$20),SUMPRODUCT(–(INDEX($A$6:$A$20,MATCH(LOOKUP(“zzzzz”,A5:$A$6),$A$6:$A$20,0)):INDEX(A6:$A$20,MATCH(TRUE,INDEX(ISTEXT(A6:$A$20),),0))=0)))&” in “&VLOOKUP(LOOKUP(“zzzzz”,$A$6:A6),$H$6:$I$8,2,0),””)
It’s great that it’s only 1 formula but trying to explain this would be a big challenge.
Example #2
=IF(A6=””,IF(C5<>””,C5,IFERROR(MATCH(“?*”,A6:$A$20,0)-1,ROWS(A6:$A$20))&” in “&VLOOKUP(A5,$H$6:$I$8,2,0)),””)
The formula above is from Bill Szysz. It’s a much easier solution to audit/modify.
Helper Formula Example
This is the solution I created. I broke the solution down into four steps:
1: Repeat Item: This helper column to repeat ‘Item’ makes it so much easier to solve =IF(A6=””,E5,A6)
2: Add countif: Counts occurrences of ‘ItemNo’ in step 1 above =COUNTIF($E$6:$E$20,H6)
3: Prep number & text: Concatenate the count (do a -1 on the count) with the text =J6-1& ” in “&I6
4: Get Answer: If and Index Match solves it =IF(B6=””,””,INDEX($K$6:$K$8,MATCH(E6,$H$6:$H$8,0)))
I created the video at least 1 month ago and it was easy to audit my solution as the steps are clearly labelled. Thanks me 🙂
YouTube Videos
Mike’s video and My video.
Excel Files
Mike’s Excel file and here is my Excel file.
About Me
My name is Kevin Lehrbass. I live in Markham, Ontario, Canada. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst.
Hundreds of millions of people use Excel everyday. Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and my blog posts.
Away from Excel I enjoy playing with my dogs Cali (she doesn’t like Excel) & Fenton, learning Spanish and playing Chess.