Find the Smallest Value in an Array based on a specific Item

Earlier today a co worker presented me with an Excel problem that he could not figure out. He wanted to find the lowest value in a table. Here is a short sample of the table he was looking at.

	A	B	C
1	ID	Product	Sale Amount
2	10001	Hammer	15.50
3	10002	Saw	7.00
4	10003	Square	11.25
5	10004	Bracket	13.00
6	10005	Hammer	11.00
7	10006	Saw	12.00
8	10007	Square	10.75
9	10008	Saw	15.00
10	10009	Hammer	12.00
11	10010	Saw	13.50
12	10011	Square	6.40
13	10012	Bracket	13.00

So I thought to myself, easy I can just do a =Min formula on column C    =MIN(C:C) and return 6.40

However then he said, he wanted the minimum value of Saws in the table. In the above example that would be 7, not 6.40.

HMMMMM. No problem, I can use the =small function.

SMALL(array,k)

Now small returns the k-th smallest value in a data set. For example, the fifth smallest number.

Since I want the smallest number k = 1. If I wanted the second smallest number I would use 2.

So I wrap the small function around an IF statement.

=SMALL(IF(B2:B13="Saw",C2:C13,""),1)

NOW TO MAKE THIS WORK, TURN THE FORMULA INTO AN ARRAY

While editing the formula, press CTRL+SHIFT+ENTER

The result is 7 since the lowest sale amount for the product SAW is 7.

If he wanted the largest I could replace =SMALL with =LARGE.

Now he could use a cell reference to pull his value. =SMALL(IF(B2:B13=H1,C2:C13,""),1)

In this instance the value in cell H1 would be looked up in the formula. Now I added the cell reference after my original solution and received errors for some of my values in the list. To correct this, I needed to recalculate my formula as an array (While editing the formula, press CTRL+SHIFT+ENTER) after setting the cell reference value.

I like this solution since it is simple. His work around was to bring the table into MS Access and then create two queries to get the answer.

He walked away happy with new knowledge and I once again sat back basking in the glow of being the Excel go to guy.

So how would you have solved this Excel question?

Extracting numbers from the middle of a mixed string (text and numbers)

We often asked to make sense of imported data. Perhaps you are asked to extract information from within a string of data. Now in another post I showed how to extract numbers from the front of a string.

However, in this example I was asked to extract the middle numbers (starting with a 1 and going 5 digits). Well I broke out my mad crazy Excel skills and after an hour I came up with a solution utilizing 2 array functions, a Left, Mid, Find and Right function. I was smugly pleased with my result but it was not eloquent even though it solved my problem.

Line Description

85363D ATLANTA    18545  785050    369634011-10-05

85364D FLORIDA    11430  785050    369662011-10-05

85365D NY         11928  785050    363672011-10-05

85366D COLUS      13535  785050    810142011-10-05

85367D AMSTERDAM  14089  785050    810152011-10-05

85368D NEVA       14331  785050    369712011-10-05

85340D GEORGIA    14331  785050    369702011-10-05

If I had taken a moment to change the font to a true type (such as courier), I would have seen the following.

Line Description

85363D ATLANTA    18545  785050    369634011-10-05

85364D FLORIDA    11430  785050    369662011-10-05

85365D NEW YORK   11928  785050    363672011-10-05

85366D COLUMBUS   13535  785050    810142011-10-05

85367D AMSTERDAM  14089  785050    810152011-10-05

85368D NEVADA     14331  785050    369712011-10-05

85340D GEORGIA    14331  785050    369702011-10-05

With a courier font, all the columns that at first appeared to not line up, in fact did line up. In addition, in  every row that I needed, there was a common denominator (785050) (The above data is just a sample of the rows I needed - they were not grouped together in the original data). To use an old saying, "I was lost in the tall grass and could not see the obvious". This one column data that I was looking at was in a grouping of about 20 other columns and the outside distraction of those other columns helped obfuscate the pattern.

Well with this clear data I could scrap all my fancy array formulas and simple extract the data with the following code.

=IFERROR(MID(A2,(FIND(785050,A2,1))-7,5),"Not found")

I added the IFERROR because there were some records that did not have 785050.

So what does this formula do?

Well…

FIND(785050,A2,1)  - checks to see if the number 785050 is in my string that I am searching

MID(A2(  - uses the FIND to return the string I am looking for.

IFERROR – used to avoid #Error or N/A errors.

-7  - since I want to return the 5 digit number and I located the starting position of 785050, I needed to look to the left 7 characters to get my starting point.

Another way to get my answer would be to break out my data with Text To Columns. Since my data is aligned (and the record layout is apparent when I converted the text to courier), Text To Columns is another great way to break out my data without coding.

So what's the lesson to learn here? First always examine your data before you begin. You want to always compare apples to apples so convert the data to a true type font such as courier to help you identify patterns. 

The other day I was asked if I could break out numbers from a cell that contained both text and numbers. My manager wanted the numbers in one column and the text to the right in the adjacent column.

I thought sure, easy to do with a combination of =Left =Mid and = Right functions. However, when I looked at the data I noticed that the numbers did not start in the same position in the text. I also noticed that some cells had 6 numbers while others had 4. To make things even more difficult, some cells started with numbers first.

Well I thought about the problem and decided to solve it by converting the formula into an array.

What’s an array formula?

An array formula is a formula that works with array data values rather than a single data value. 

There are two different types of array formulas

1.     Formulas that work with an array or series of data and aggregate the result. Sum, Average and Count are good examples of this type as they all return a single value to a single cell.

2.     Formulas that returns an array of numbers as its result. These array formulas are entered into multiple cells that are then treated as a group. For example, =ROW(A1:E20).

In the above example I have my original data in column A. I first use the formula in column B to extract the numbers in the string. After entering the formula, I hold down the control key, shift key and enter key all at the same time to convert the formula into an array.

Once I had that, it was a simple matter of getting the length of how many numbers were in the original data and the starting position of the numbers in the string (Columns C and D).

Based on that, I could then parse out the text to the right of the number (Column E).

Now to accomplish all this I needed to use 7 functions….

=IF

=MID

=MATCH

=ROW

=LEN

=COUNT

=ISNUMBER

All of these functions are simple to use but the trick was combining them to get my solution.

Indeed knowing what functions are available in Excel will allow you to join them to solve almost any problem you have to solve. So it’s a good idea to learn them or at least know they exist so that you can reference them.

{=1*MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$100),1)),0),COUNT(1*MID(A2,ROW($1:$100),1)))}

{=MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($2:$9),1)),0)}

{=IF(MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($2:$9),1)),0) = 1,MID(A2,(E2+D2),90),MID(A2,(E2+D2+1),90))}

How would you have solved the above problem?