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

**olives** · 02-14-2013, 11:26 PM

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

**Rick Rothstein** · 02-14-2013, 11:54 PM

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

**olives** · 02-15-2013, 03:40 AM

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!

example.xls

O.

**Rick Rothstein** · 02-15-2013, 04:22 AM

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))

**olives** · 02-15-2013, 04:46 AM

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!

**olives** · 02-15-2013, 12:53 AM

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

**olives** · 02-16-2013, 02:15 AM

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!

**Rick Rothstein** · 02-16-2013, 03:29 AM

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

**olives** · 02-17-2013, 11:17 PM

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!