How to search in a column for particular value in excel

Question

I am totally confused what to do in this scenario. Any help would be great.

I have three column and in first column I have Name with duplicate entry and for that I have another column as Role and its respective value (PRIMARY / SECONDARY). Now I am trying to get status Value Yes or No on the basis of Column A and B. If for Name in column A has Role as PRIMARY then Status should be Yes otherwise No. Means if in Column A Name has no Primary Value for any of the record then the Status should be NO. Below is the example for reference.

Name            Role        Status
C7E748588B6D    PRIMARY     Yes
C7E748588B6D    SECONDARY   Yes
C7E748588B6D    SECONDARY   Yes
C7E748588B6D    SECONDARY   Yes
C7E748588B6D    SECONDARY   Yes
5E0463D34209    SECONDARY   No
5E0463D34209    SECONDARY   No
5E0463D34209    SECONDARY   No
5E0463D34209    SECONDARY   No
46C14AEC2EB5    PRIMARY     Yes
46C14AEC2EB5    SECONDARY   Yes
46C14AEC2EB5    SECONDARY   Yes
46C14AEC2EB5    SECONDARY   Yes
46C14AEC2EB5    SECONDARY   Yes
A6C883388E3D    PRIMARY     Yes
A6C883388E3D    SECONDARY   Yes
A6C883388E3D    SECONDARY   Yes

Thanks.

Does this need to be VBA? This could be done with a formula, assuming your Status Yes/No column is in column C, and row 1 is a header row so your actual data starts on row 2, then in cell C2 and copied down would be this formula: `=IF(COUNTIFS(A:A,A2,B:B,"PRIMARY")>0,"Yes","No")` — tigeravatar, Aug 18 '16 at 18:59

phil652 · Answer 1 · 2016-08-18T20:12:52.637

0

EDIT: Changed my answer following the comments bellow.

This is probably not the easiest way but it should work

Dim l As Long
Dim name As String
Dim cnt As Integer

l = 1
cnt = 0

For l = 1 To ActiveSheet.UsedRange.Rows.Count

    If Cells(l, 2).Value = "PRIMARY" Then
        name = Cells(l, 1)
        Do While (Cells(l + cnt, 1) = name)
            Cells(l + cnt, 3).Value = "YES"
            cnt = cnt + 1
        Loop
        cnt = 0
    ElseIf Cells(l, 3).Value = "" Then Cells(l, 3).Value = "No"
    End If

Next l

edited Aug 18 '16 at 20:12

answered Aug 18 '16 at 18:55

phil652

1,484
1
23
48

This will only set rows with the word "PRIMARY" to show Yes. OP wants all rows of repeated name to show Yes if it has a Primary, per his provided example. – tigeravatar Aug 18 '16 at 19:00
And `Cells` uses `(rows, columns)`. It looks like you have your indices reversed – PartyHatPanda Aug 18 '16 at 19:05
@tigeravatar If all rows that have repeated name show Yes then he will never get a value of No – phil652 Aug 18 '16 at 19:20
@phil652 OP *wants* all rows that both A) have a repeated name **AND** B) have *at least* one row in column B with a value of "PRIMARY" to show "Yes". If the name does not have any row in column B with a value of "PRIMARY" (it ONLY has "SECONDARY" in all of that name's rows), then all of those rows for that name should be "No". In OP's provided example, `5E0463D34209` is the only name whose set of rows are all "No" because it is the only name without a Primary. – tigeravatar Aug 18 '16 at 19:25
The code you have will set the second row of the OPs sample to be a "NO", because Cells(l, 2).Value is "SECONDARY", but it should be set to "YES" due to the first row of data. (Your code will actually set the second row to "YES" when processing the first row, but then set it to "NO" when it tries to process the second row.) – YowE3K Aug 18 '16 at 20:03
phil652 answer does what OP asked for, in a short and understandable code. For a more generalized (but unfortunatelly somewhat obscure) piece of code, see [this answer](http://stackoverflow.com/questions/37777641/excel-vba-place-values-in-multidimensional-array/37777993#37777993) to a question I made some time ago. It proves to be pretty robust. Some modifications should be done to fit OP requirements. – CMArg Aug 18 '16 at 20:45

score 0 · Answer 2 · answered Aug 18 '16 at 20:58

This should work no matter what order the data is in. It is written for your 18 lines you've provided. You will of course have to update the worksheet variables and the control for the For loop to your specific application.

Option Explicit
Sub PrimaryCheck()

Dim i As Integer
Dim w1 As Worksheet
Set w1 = ThisWorkbook.Sheets(1)
Dim SearchString As Range
Dim SearchRange As Range
Dim SearchAddress As String
Dim FoundRange As Range

Set SearchRange = w1.Range(w1.Cells(1, 1), w1.Cells(18, 1))


For i = 2 To 18
    If IsEmpty(w1.Cells(i, 3)) Then
        If w1.Cells(i, 2).Value = "PRIMARY" Then
            w1.Cells(i, 3) = "YES"
        ElseIf w1.Cells(i, 2).Value = "SECONDARY" Then
            Set SearchString = w1.Cells(i, 1)
            SearchAddress = w1.Cells(i, 1).Address
            Set FoundRange = SearchRange.Find(SearchString, w1.Cells(i, 1), , , , xlNext, True)
            Do While Not FoundRange Is Nothing And FoundRange.Address <> SearchAddress

                If w1.Cells(FoundRange.Row, 2).Value = "PRIMARY" Then
                    w1.Cells(i, 3) = "YES"
                End If
                Set FoundRange = SearchRange.FindNext(FoundRange)
            Loop
            If IsEmpty(w1.Cells(i, 3)) Then
                w1.Cells(i, 3).Value = "NO"
            End If
        End If
    End If
Next i
End Sub

How to search in a column for particular value in excel

2 Answers2