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

[tonywatsonhelp]

Hi everyone,
need some help please,
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,

=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

 

[NeonRedSharpie]

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]

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

 

[tonywatsonhelp]

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]

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

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]

Brilliant, thank you very much,
both work perfect, you have been a massive help.

Thanks

Tony