Excel formula to compare single value in one cell with multiple values in other cell

Question

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?

Solution 1

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!

Solution 2

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.

Comments

[Dave]

You specify worksheet function, does this mean you don't want VBa?

[Raw]

I welcome anything that can solve this for me...

[Raw]

Yes, the column is already set as Text. Dave thanks for the solution but I dont know how to run VBa. May be its quite easy for you or for me to google that but for first time I need a demo and what better than asking to a expert like you.

[Dave]

superuser.com/questions/801609/how-do-i-add-vba-to-my-ms-off‌​ice/…

[Raw]

i dnt have that rep to continue chat.. I will check that link in the above comment posted by you..and will let you know my progress.

[Raw]

Its working...thanks @Dave. It solved 1st part of my problem.

[Raw]

Actually I have 3 columns: Column A has a value, Column B have a set of values which showing the start point of an array, Column C having a set of values which showing the corresponding end points of that array. We have to check whether the value in Column A is part of any array or not.

[Raw]

Thanks Steve but it seems so confusing. And I have upto 130 comma separated values in a single cell. Anyway I move don to next step (thanks to Dave). Now I have two columns with multiple entries of Yes, No. I want to concatenate the corresponding values in the 3rd column. say column A has Yes, Yes, No, No and Column B has Yes, No, No, No. So in column C it should be YesYes, YesNo, NoNo, NoNo.

[Steve]

I agree that it's confusing – particularly if you have lots of comma-separate values. It's not as good a solution as using VBA, unless something about your situation makes VBA not an option. For example, if you're using some other software, such as Google Spreadsheet, that doesn't have VBA.

[Steve]

Another thought: Why do you need to place the comma-separated values into spreadsheet cells at all? Wouldn't it make more sense to import them as a spreadsheet page, and leave it to the spreadsheet's import features to process the comma separations? Is there a problem with doing it that way, or is this an intellectual exercise rather than a practical problem?

[Mike Honey]

@Raw you are wasting peoples time which is rude at the least. Kindly update your question to reflect this info which you should've included up front.

[Raw]

its a part of research work where I have to find out whether the first value in Column A falls between the first value of Column B and column C (including both the values) or between the 2nd values of Column B and C, or between the third value of column B and C..or so so..

[Dave]

@Raw, there is no reason you can't ask a new question. Explain it leads on from this, but you need to look at columns. Please try something first, but add a new question, it's not difficult to resolve it :)

[Mike Honey]

I've edited my answer above to reference a working prototype which I have created for this.

[Mike Honey]

@Raw note the add-in I used for this is Power Query (not Pivot Query).

[Mike Honey]

@Raw you have confused the issue by asking one question then switching the requirements in your comments. There are two pairs of sheets in that file. The two sheets ending "LTEQ" show <= logic. The two sheets ending "between" show between logic. There are two separate Power Query Queries for the two scenarios. Take your pick when you decide what your requirements actually are.

[Raw]

Thank you Mike. and sorry for my faults. 2 points I want to say now. 1st in my data the comma separated values in each cell are in ascending order without any repeats. 2nd in your Power Query Demo, the final value in the last sheet's D2 cell should be Yes, Yes, Yes, No, then only it will be correct.

[Mike Honey]

@Raw, WRT the last sheet's last row, I think you have misread my input data. The third comparison is 13 <= 12 <= 12, which is not true. Feel free to download the file and edit the input data or add your own rows to it to test it out.