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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,574
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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,
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.
Dji
Valuenumbersmall / large/tinyresults I want is the highest number of all of them that say large but only within there number group.
3402large50
4302large50
5502large50
6202large50
7503small
8403small
9203small
10303small
11404large60
12504large60
13604large60
14204large60
15205tiny
16305tiny
17205tiny
18305tiny
19406large40
20206large40
21306large40

<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
 

Some videos you may like

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
Joined
Jul 14, 2014
Messages
1,678
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Feb 24, 2014
Messages
2,574
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Jul 14, 2014
Messages
1,678
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
Joined
Feb 24, 2014
Messages
2,574
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Brilliant, thank you very much,
both work perfect, you have been a massive help.

Thanks

Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,109,042
Messages
5,526,420
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top