I have a value in Column A, which I want to compare with multiple values in column B, and depending on that value, put the answer in column C.
For example, using the table below, it searching in column B for values which are less than or equal to 12 and put the answer in same order in column C.
Column A Column B Column C
12 0,12,13,14 Yes, Yes, No, No
101 101,102,103,104 Yes, No, No, No
How can I do this in Excel?
This does exactly what you want.
Option Explicit
Sub DoTheThing()
Dim row As Integer
row = 1 ' WHAT IS THE STARTING ROW
Do While (Range("A" & row).Value <> "")
Dim vals() As String
vals = Split(Range("B" & row).Value, ",")
Dim lookUpValue As String
lookUpValue = Range("A" & row).Value
Dim result As String
result = ""
Dim i As Integer
For i = 0 To UBound(vals)
If CSng(lookUpValue) >= CSng(vals(i)) Then
result = result & "Yes, "
Else
result = result & "No, "
End If
Next i
result = Trim(result)
result = Left(result, Len(result) - 1)
Range("C" & row).Value = result
row = row + 1
Loop
End Sub
My worksheet looked like
And after I run the VBa
Excel kept formatting the columns as number. It must remain as Text!
The way I would go about it would be to start by breaking up the problem into a bunch of columns, each with a piece of the problem. For example:
A B C D E F G H I J K L M N
1 12 0,12,13,14 2 5 8 0 12 13 14 Yes Yes No No Yes, Yes, No, No
2 101 101,102,103,104 4 8 12 101 102 103 104 Yes No No No Yes, No, No, No
These are the expressions for C1 through N1:
C1 =FIND(",",B1) D1 =FIND(",",B1,C1+1) E1 =FIND(",",B1,D1+1)
F1 =LEFT(B1,C1-1)+0 G1 =MID(B1,C1+1,D1-C1-1)+0
H1 =MID(B1,D1+1,E1-D1-1)+0 I1 =RIGHT(B1,LEN(B1)-E1)+0
J1 =IF(F1<=$A1,"Yes","No") K1, L1, M1 (copy from J1)
N1 =J1&", "&K1&", "&L1&", "&M1
If case it's not obvious, the "+0" is a handy way to force a text value into a number, so that the comparisons in I, J, K, and L are done as numeric comparisons rather than as text.
For C2 through N2, copy from C1 through N1.
If you don't want to use extra columns, you can join the results from the multi-column version into one giant, complicated expression in a single column. It's easier to do that in several steps. For example, the first step would be to combine the FIND expressions with the string expressions. Here's some code for that:
F =LEFT(B1,FIND(",",B1)-1)+0
G =MID(B1,FIND(",",B1)+1,FIND(",",B1,FIND(",",B1)+1)-FIND(",",B1)-1)+0
H =MID(B1,FIND(",",B1,FIND(",",B1)+1)+1,FIND(",",B1,FIND(",",B1,FIND(",",B1)+1)+1)-FIND(",",B1,FIND(",",B1)+1)-1)+0
I =RIGHT(B1,LEN(B1)-FIND(",",B1,FIND(",",B1,FIND(",",B1)+1)+1))+0
Those are pretty hideous, because the uses of E uses D, which uses C, and those are used several times by G, H, and I. Putting all the intermediate results into hidden columns saves a lot of duplicated expressions.
Things get worse if you want to go beyond four comma-separated number in column B, but the way to add columns should be pretty obvious.
Allowing B to have a variable number of comma-separated numbers is not so obvious. The trick is to add some IF statements, testing for error conditions. That raises one final point, that this doesn't include any error checking other than what's built into Excel. A robust spreadsheet should include at least some error checking.
I would use the Power Query Add-In for this. It has Split and Combine commands that can convert delimited text e.g. 0,12,13,14 into a list and back.
I've built a prototype which you can view or download - its "Power Query demo - compare single value in one cell with multiple values in other cell.xlsx" in my One Drive:
https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398
Within that file I've included two solutions - one for comparing using "Less than or equal to", and another for comparing using "between" logic.
It takes a few Query steps to get there, and for some steps the generated code needs to be edited. But for 90% of the steps you just click around in the Power Query UI.
