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?....