# can someone please imporove on this formula as its an array and using up loads of my memmory

#### tonywatsonhelp

##### Well-known Member
Hi everyone,
I have a list of values in column D anything up to 10000 rows, and a set of numbers in column J and the words "Large" "Small" or "Tiny" in column I,
The following array formula does exactly what I want, but I'm copying it down 10000 lines so its just not running,
Code:
``=IF(I3="Large",MAX(IF(\$J\$3:\$J\$10000=J3,\$D\$3:\$D\$10000)),"")``

basically what i'm trying to do is find the highest value of the data in D for the cells that match the number thats in column I,
I've done a small diagram to show the results I need,
I don't mind if this is a macro or formula , I just need a solution please.
 D j i Value number small / large/tiny results I want is the highest number of all of them that say large but only within there number group. 3 40 2 large 50 4 30 2 large 50 5 50 2 large 50 6 20 2 large 50 7 50 3 small 8 40 3 small 9 20 3 small 10 30 3 small 11 40 4 large 60 12 50 4 large 60 13 60 4 large 60 14 20 4 large 60 15 20 5 tiny 16 30 5 tiny 17 20 5 tiny 18 30 5 tiny 19 40 6 large 40 20 20 6 large 40 21 30 6 large 40

<TBODY>
</TBODY>
all the data will be together as you can see, but it could be any number of rows.

I hope someone can help me, been struggling with this all day!
Thanks

Tony

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### NeonRedSharpie

##### Well-known Member
Code:
``````Sub highestNumberInGroup()

startYes = True

For x = 2 To Cells(Rows.Count, 4).End(xlUp).Row + 1 Step 1
Select Case Cells(x, 9)
Case "large"
If startYes = True Then startRow = x
If Cells(x, 4) > maxVal Then maxVal = Cells(x, 4)
startYes = False
Case Else
If startYes = False Then
Range(Cells(startRow, 12), Cells(x - 1, 12)) = maxVal
startYes = True
maxVal = 0
End If
End Select
Next x
End Sub``````

I just wrote this macro earlier today and it does exactly what you're looking for.

#### barry houdini

##### MrExcel MVP
In your sample in column J you have four 2s, four 3s etc. What's the largest group in the real data?

#### tonywatsonhelp

##### Well-known Member
Hi Neonredsharpie,
That is perfect thank you very much,
now if you don't mind, could you just make one small change for me? instead of the highest value, how would I get the lowest value?

Thanks

Tony

#### NeonRedSharpie

##### Well-known Member
Hi Neonredsharpie,
That is perfect thank you very much,
now if you don't mind, could you just make one small change for me? instead of the highest value, how would I get the lowest value?

Thanks

Tony

Magic

Code:
``````Sub lowestNumberInGroup()

startYes = True
minVal = False

For x = 2 To Cells(Rows.Count, 4).End(xlUp).Row + 1 Step 1
Select Case Cells(x, 9)
Case "large"
If minVal = False Then minVal = Cells(x, 4)
If startYes = True Then startRow = x
If Cells(x, 4) < minVal Then minVal = Cells(x, 4)
startYes = False
Case Else
If startYes = False Then
Range(Cells(startRow, 12), Cells(x - 1, 12)) = minVal
startYes = True
minVal = False
End If
End Select
Next x
End Sub``````

#### tonywatsonhelp

##### Well-known Member
Brilliant, thank you very much,
both work perfect, you have been a massive help.

Thanks

Tony

Replies
5
Views
61
Replies
6
Views
65
Replies
4
Views
53
Replies
12
Views
93
Replies
3
Views
27