Looking for Value in Another Column, Evaluating and Then Looking in Another Column

I have a value say in Cell A1.

I want to compare the value in Cell A1 with all the values in the cells in Column B (which may have thousands).

I am interested in all the values in Column B which are lower than the value in cell A1.

If a cell value in Column B is lower than the value in A1, then I want to go to the adjacent column, Column C, and see the value of that cell, the one adjacent to the one in Column B.

Now I want to compare the values in Column C and pick the 10 or 15 highest and be put in cells A2, A3, A4, etc..

Can this be done?

(This is my first post here. I followed Rick Rothstein here.)

Welcome to the forum olives (thanks for following me here).

Install this UDF (user defined function) in a module...

Code:

---

Function Smallest(Number As Long) As Variant
  Dim X As Long, Index As Long, TestValue As Variant, Source As Variant, Results As Variant
  Source = Range("B1:C" & Cells(Rows.Count, "B").End(xlUp).Row)
  ReDim Results(1 To UBound(Source), 1 To 1)
  TestValue = Range("A1").Value
  For X = 1 To UBound(Source)
    If Source(X, 1) < TestValue Then
      Index = Index + 1
      Results(Index, 1) = Source(X, 2)
    End If
  Next
  Smallest = WorksheetFunction.Small(Results, Number)
End Function

---

Then put this formula in A2 and copy it down for as many rows as you want...

=Smallest(ROW(A1))

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Smallest just like it was a built-in Excel function (as show in the example formula above).

I'll be checking it tonight with my data at home.. Thanks! :)


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839t UQl_92mvg
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Ok, we're almost there... I've attached an example file, maybe what I want is clearer in the attached spreadsheet than what I wrote.... Thanks!

Attachment 602

O.

Quote:

---

Originally Posted by olives

Ok, we're almost there... I've attached an example file, maybe what I want is clearer in the attached spreadsheet than what I wrote.... Thanks!

---

Sorry, I don't know why, but I misread your original message to ask for the smallest values from Column C, but it is obvious that you did in fact ask for the highest values. Replace the code I gave you earlier with this...

Code:

---

Function Largest(Number As Long) As Variant
  Dim X As Long, Index As Long, TestValue As Variant, Source As Variant, Results As Variant
  Source = Range("B1:C" & Cells(Rows.Count, "B").End(xlUp).Row)
  ReDim Results(1 To UBound(Source), 1 To 1)
  TestValue = Range("A1").Value
  For X = 1 To UBound(Source)
    If Source(X, 1) < TestValue Then
      Index = Index + 1
      Results(Index, 1) = Source(X, 2)
    End If
  Next
  Largest = WorksheetFunction.Large(Results, Number)
End Function

---

And then use this formula in A2 on down...

=Largest(ROW(A1))

Quote:

---

Originally Posted by Rick Rothstein

... use this formula in A2 on down...

---

Wow, now it works beautifully! You're gifted! Now on to my full data sheet, thanks, Rick!

Rick, I like the formula and it's working well, but now I want to move data around and I think the reference to cell A1 is hindering me. Is there any way to make the formula and/or the code more 'generic' and have it search the next two adjacent columns to where I place the formula instead of always placing the number I want compared to in A1? I know I said A1 above, but it'd be nice to be bale to place the formula anywhere and have it search in the next two columns, as above, first to check if the number is below the comparison value and then to check for thehighest value items... Thanks!

[QUOTE=olives;3609]...but now I want to move data around and I think the reference to cell A1 is hindering me. Is there any way to make the formula and/or the code more 'generic' and have it search the next two adjacent columns to where I place the formula instead of always placing the number I want compared to in A1?
[QUOTE]
Okay, I think this will work the way you want, but note that the argument list to the function is completely different. First I'll give you the code, then I'll explain how to use it.

Code:

---

Function Largest(CompareValue As Double, TopLeftNumber As Range) As Variant
  Dim X As Long, Index As Long, TestValue As Variant, Source As Variant, Results As Variant
  Source = TopLeftNumber.Resize(Cells(Rows.Count, TopLeftNumber.Column).End(xlUp).Row - TopLeftNumber.Row + 1, 2)
  ReDim Results(1 To UBound(Source), 1 To 1)
  For X = 1 To UBound(Source)
    If Source(X, 1) < CompareValue Then
      Index = Index + 1
      Results(Index, 1) = Source(X, 2)
    End If
  Next
  Largest = WorksheetFunction.Large(Results, Application.Caller.Row - TopLeftNumber.Row + 1)
End Function

---

The first instance of this UDF must be placed on the same row as the first (topmost) number in your two columns of numbers. So, if your two columns of numbers were in C3:D100, the first instance of the formula must be placed in a cell on Row 3 (it does not have to be adjacent to the two columns of numbers). Now, for the arguments. The first argument is the value you want to compare (what used to be in A1 originally). You can specify it as a constant or a cell reference (but if you specify it as a cell reference, then the row number must be absolute (that is, there needs to be a $ sign in front of the number) so it won't change as the UDF is copied down. The second argument is a reference to the top left cell of the two columns of numbers being compared and the row number must also be absolute (that is, there needs to be a $ sign in front of the number) so it won't change as the UDF is copied down. So, if the two columns of numbers were in C3:D100, and the comparison number were 100, then the formula would look like this...

=Largest(100,C$3)

On the other hand, if your compare value was placed in F2, then the formula would look like this...

=Largest(F$2,C$3)

Note you do not specify the full range on that second argument, only the top left cell address... the code will calculate the row containing the last value for the two columns of numbers on its own.

Does this modification do what you want?

Ok! :) I'll give it all a try tomorrow and give you a shout back! Awesome, Rick, awesome! :)

I'm half asleep, but, does this formula do what the original did? If I paste it below the 1st time will it give me the 2nd best, or highest, value?....

Quote:

---

Originally Posted by olives

I'm half asleep, but, does this formula do what the original did? If I paste it below the 1st time will it give me the 2nd best, or highest, value?....

---

Remember I said "The first instance of this UDF must be placed on the same row as the first (topmost) number in your two columns of numbers"... the reason is the formula needs a starting point to calculate from, so I used the top row of the two columns of numbers. From there, you can copy the formula down as far as you like. So, the highest value will be on the same row as the top cell of the two columns of numbers, the second highest will be under that one and so on.

Quote:

---

Originally Posted by Rick Rothstein

... Does this modification do what you want?

---

Ok, I don't know if I'm doing anything wrong, but I set up the function and when I have it read =Largest(D$1, P$1) it does give me the highest value in Column Q but if I change the value in D$1, I'm getting the #VALUE error. I can't tell if it's working ...

I also don't get any values below where I insert the function if I copy it below the first instance. I get the #VALUE error again.

Would you like me to set up and upload a dummy data sheet?...

And, I don't know anything about the functions, but, to make it even more 'portable', I know you have the function default to two contiguous columns, but also be able to have it look something ike this: ?


=Largest(F$2, C$3, G$3)

Meaning:

1. Compare the value in Cell F$2 with all the values in Column C, then for all the values in Column C that are below the value in F$2, go to the same row in Column G, compare those values and give me the highest, etc...?

Where I could designate those columns? Just wondering... I gotta learn how to program! Thanks!

Quote:

---

Originally Posted by Rick Rothstein

... I said "The first instance of this UDF must be placed on the same row as the first (topmost) number in your two columns of numbers"... the reason is the formula needs a starting point to calculate from, so I used the top row of the two columns of numbers. From there, you can copy the formula down as far as you like. So, the highest value will be on the same row as the top cell of the two columns of numbers, the second highest will be under that one and so on.

---

I hadn't read the above before sending out my last message, but yes, I have them lined up as you have described.

Quote:

---

Originally Posted by olives

Would you like me to set up and upload a dummy data sheet?...

---

Yes, please.

Ok, Rick, I set up the example. The formula is working fine in it. I guess I did something wrong when I set it all up before. I'll upload an example later tonight since the example I have right now is too big to upload. Thanks, Rick! :)

Ok, here we go. Sorry this took so long, here's the example with questions:

Attachment 607


After the first setup I guess I can drag the "Results" cells anywhere, right? Thanks, Rick! :)

Quote:

---

Originally Posted by olives

... Sorry this took so long...

---

Rick, I guess my main question is, can I designate the 2nd column, instead of relying on the fact that, as they stand, the columns I'm looking at, are contiguous? Thanks! :)

Rick, how can I change your code so I have more flexibility with the placement of the columns? Right now it works fine, but if I wanted to put several columns of data between the two columns I'm evaluating, I'd probably lose the functionality of your code as it is written, right?... Thanks! :)

Quote:

---

Originally Posted by olives

Rick, how can I change your code so I have more flexibility with the placement of the columns?

---

I've been trying to figure this one out...

Quote:

---

Originally Posted by olives

I've been trying to figure this one out...

---

Sorry about my absence from this forum. I just wanted to check if you were still following this thread before I attempted to modify my code in resposnse to the questions you asked inside of your posted workbook.

Quote:

---

Originally Posted by Rick Rothstein

Sorry about my absence...

---

Yes, please! Welcome back! Thanks!

Quote:

---

Originally Posted by olives

Yes, please! Welcome back! Thanks!

---

Give this UDF a try...

Code:

---

Function Largest(CompareValue As Double, FirstNumberCell As Range, _
                Optional SecondNumberCell As Range, _
                Optional ReferenceColumn As Long = 1) As Variant
  Dim X As Long, Index As Long, TestValue As Variant, RefCell As Range
  Dim Source1 As Variant, Source2 As Variant, Results As Variant
  Application.Volatile
  If SecondNumberCell Is Nothing Then Set SecondNumberCell = FirstNumberCell.Offset(, 1)
  Source1 = FirstNumberCell.Resize(Cells(Rows.Count, FirstNumberCell.Column). _
                                  End(xlUp).Row - FirstNumberCell.Row + 1, 2)
  Source2 = SecondNumberCell.Resize(Cells(Rows.Count, SecondNumberCell.Column). _
                                    End(xlUp).Row - SecondNumberCell.Row + 1, 2)
  ReDim Results(1 To UBound(Source1), 1 To 1)
  For X = 1 To UBound(Source1)
    If Source1(X, 1) < CompareValue Then
      Index = Index + 1
      Results(Index, 1) = Source2(X, 1)
    End If
  Next
  If ReferenceColumn = 1 Then
    Set RefCell = FirstNumberCell
  Else
    Set RefCell = SecondNumberCell
  End If
  Largest = WorksheetFunction.Large(Results, Application.Caller.Row - RefCell.Row + 1)
End Function

---

Note the argument list has changed. The first argument is the number or reference to a cell containing the number you want to be lower than, the second argument is a reference to the cell containing the first number in the search column, the third argument is a reference to the cell containing the first number in the result column (the source and results lists do not have to both start on the same row), and the last argument is either a 1 or 2 depending on which column's first number the formula is aligned to (1 means the formula is on the same row as the source column's first number and 2 means the formula is on the same row as the result column's first number). The third argument is optional and if omitted, the result column defaults to the cells immediately to the right of the source cells (that makes it function exactly like my first posted UDF). The fourth argument is also optional and defaults to 1 (the source cells). So, assuming this...

A1 - 100
F3:F18 - contains source values to check against A1's value
J3:J18 - contains result values where the largest value will be returned from
M3 -=Largest(A1,F3,J3)

However, for this arrangement...

A1 - 100
F3:F18 - contains source values to check against A1's value
J23:J38 - contains result values where the largest value will be returned from
M23 -=Largest(A1,F3,J23,2)