Find all results of an unique group with a formula in an excel report
In one of my many reports, I needed to display all data of a same value, for example, for a particular team group, I needed to find all its corresponding activities.
When I use the formula ?
To display all the results of an unique value.
How to use the formula ?
The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".
For the formula to work properly, I need to do an additional action, by pressing “shift + control + enter” in order to put the formula between brackets. If not, it will show an error.
How is/are the formula(s) ?
=SMALL()
=IF()
=ROW()
=INDEX()
To know the row ID, put this formula:
=SMALL(IF(A:A=D2,ROW(A:A)),ROW(1:1))
Then pressing “shift + control + enter” to put it between brackets so I will get the result and not an error:
{=SMALL(IF(A:A=D2,ROW(A:A)),ROW(1:1))}
After that, just copy it down to the other cells and the formula will be updated automatically.
Explanation:
- D2 is where I put the group name to get the result I want
- A:A is the column letter where it is looking for the group
- 1:1 is the row number where it is starting to look for
The result is on the column D.
To display the corresponding results:
=INDEX(A:B,SMALL(IF(A:A=D2,ROW(A:A)),ROW(1:1)),2)
As for the other, press “shift + control + enter” and the result is on the column E. To display the name of the category, I need to reference the column B with the number 2 at the end of the formula.
Interesting Topics
-
Be successfully certified ITIL 4 Managing Professional
Study, study and study, I couldn’t be successfully certified without studying it, if you are interested...
-
Be successfully certified ITIL 4 Strategic Leader
With my ITIL 4 Managing Professional certification (ITIL MP) in the pocket, it was time to go for the...
-
Hide visual and change background color based on selection
Some small tricks to customize the background colour of a text box...
-
Stacked and clustered column chart or double stacked column chart
In excel, I use a lot the combination of clustered and stacked chart...