Match and Large using non contiguous range

[stephall]

Hi, I'm trying to find the row number of the cell that has the value 22 in a dynamic non contiguous range that i have stored in cell J18 using the Match function.

I would also like to find the 4th largest number is the in range using the Large Function.

I had found on a forum that a function could be added using VBA to convert a non contiguous range to a contiguous range.

The code for that function is :

Function Arrange(rng As Range) As Variant
Dim temp As Variant
Dim i As Long
Dim r As Range

ReDim temp(1 To rng.Cells.Count)
i = 1

For Each r In rng
temp(i) = r.Value
i = i + 1
Next r

Arrange = Application.Transpose(temp)
End Function


This was giving me some issues though since what I had in cell J18 is a text so I found another function that converts the text to a range :


Function TXT2RNG(text) As Variant

Set TXT2RNG = Range(text)

End Function

I then tried to combine these together to achieve my goal.

In cell J18 I have Sheet1!E71:E94,Sheet1!E181:E201

When i try =Arrange(Sheet1!E71:E94,Sheet1!E181:E201)
I get a #Value error.
When i try
=Arrange((Sheet1!E71:E94,Sheet1!E181:E201))
It works without error

When i try
=Arrange(J18)
it just returns the text Sheet1!E71:E94,Sheet1!E181:E201

When i try
=Arrange(TXT2RNG(J18))
It works without error

Now when i try

=LARGE(Arrange((Sheet1!E71:E94,Sheet1!E181:E201)),4)
and also
=MATCH(22,Arrange((Sheet1!E71:E94,Sheet1!E181:E201)), 0)

They work

But if I try using the cell reference J18 instead of directly inputting Sheet1!E71:E94,Sheet1!E181:E201
It doesn't work, even if try including the TXT2RNG function.

 

[DanteAmor]

Hi @stephall .

Thaks for posting on MrExcel.​

Try this, for cell J18:

=LARGE(Arrange(J18),4)

Or this, but in quotes the ranges:

=LARGE(Arrange("Sheet1!E71:E94,Sheet1!E181:E201"),4)

Please update the code to this:

Function Arrange(myRng As String) As Variant
  Dim temp As Variant
  Dim i As Long
  Dim r As Range, rng As Range
  
  Set rng = Range(myRng)
  
  ReDim temp(1 To rng.Cells.Count)
  i = 1
  For Each r In rng
    temp(i) = r.Value
    i = i + 1
  Next r
  
  Arrange = Application.Transpose(temp)
End Function

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[stephall]

Hi @stephall .

Thaks for posting on MrExcel.​

Try this, for cell J18:

=LARGE(Arrange(J18),4)

Or this, but in quotes the ranges:

=LARGE(Arrange("Sheet1!E71:E94,Sheet1!E181:E201"),4)

Please update the code to this:

Function Arrange(myRng As String) As Variant
  Dim temp As Variant
  Dim i As Long
  Dim r As Range, rng As Range
 
  Set rng = Range(myRng)
 
  ReDim temp(1 To rng.Cells.Count)
  i = 1
  For Each r In rng
    temp(i) = r.Value
    i = i + 1
  Next r
 
  Arrange = Application.Transpose(temp)
End Function

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

It worked, thank you!
I have 1 other issue that's separate in nature from what we just dealt with. I used the large function to get the 4th largest value which was 22 and then used the match function to find the position of the row in the range to return fields from various columns within that row. In column F I have some company names, G I have some Rating types (High, Medium, Low) and in column H I have some rating scores (numbers).

As stated before I have some dynamic non contiguous ranges that i have stored in cells.
In J18 there is Sheet1!E71:E94,Sheet1!E181:E201
In J19 there is
Sheet1!F71:F94,Sheet1!F181:F201
In J20 there is
Sheet1!G71:G94,Sheet1!G181:G201
In J21 there is
Sheet1!H71:H94,Sheet1!H181:H201

When i do
INDEX(Arrange(J18) I get 22 which is correct
INDEX(Arrange(J20) I get the associated rating type which is correct
INDEX(Arrange(J21) I get the associated rating score which is correct

But when i try
INDEX(Arrange(J19) I'm getting a #Value error.

 

[stephall]

It worked, thank you!
I have 1 other issue that's separate in nature from what we just dealt with. I used the large function to get the 4th largest value which was 22 and then used the match function to find the position of the row in the range to return fields from various columns within that row. In column F I have some company names, G I have some Rating types (High, Medium, Low) and in column H I have some rating scores (numbers).

As stated before I have some dynamic non contiguous ranges that i have stored in cells.
In J18 there is Sheet1!E71:E94,Sheet1!E181:E201
In J19 there is
Sheet1!F71:F94,Sheet1!F181:F201
In J20 there is
Sheet1!G71:G94,Sheet1!G181:G201
In J21 there is
Sheet1!H71:H94,Sheet1!H181:H201

When i do
INDEX(Arrange(J18) I get 22 which is correct
INDEX(Arrange(J20) I get the associated rating type which is correct
INDEX(Arrange(J21) I get the associated rating score which is correct

But when i try
INDEX(Arrange(J19) I'm getting a #Value error.

Sorry I left off parts of the formulas they are supposed to be
INDEX(Arrange(J18),40)
INDEX(Arrange(J20),40)
INDEX(Arrange(J21),40)
INDEX(Arrange(J19),40)

 

[DanteAmor]

In J19 there is
Sheet1!F71:F94,Sheet1!F181:F201


But when i try
INDEX(Arrange(J19) I'm getting a #Value error.

But it is not function problem.
It's a problem in your data in column F.
Maybe you have an error in the cell you are trying to read.

 

[stephall]

But it is not function problem.
It's a problem in your data in column F.
Maybe you have an error in the cell you are trying to read.

Don't see any errors in the column, the data type is text.

 

[DanteAmor]

With the data that I am testing I have no problems.

And I can only guess what problem you have in your data.

Delete all the data from column F, copy the data from column E (these do work) and paste it into column F.

The above is for you to rule out the function problem and see that the problem is in your data.

If you can't find the problem, then share your file so I can review your data.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.