Extract values from range basis cell values.

[shantraj.antin@gmail.com]

Hi All,


I have 4 tasks A,B,C,D and its serial number in column A. Based on the "Yes or No" values of each task I am looking for its serial numbers in summary table.


Example: If A task value is “No” @ serial number 4,8 and 499, I need to extract it in summary table under task A.

I have uploaded the sample file for your reference.

Thank you

Regards,

Shan

 

[Ratan Bhushan]

Hi All,


I have 4 tasks A,B,C,D and its serial number in column A. Based on the "Yes or No" values of each task I am looking for its serial numbers in summary table.


Example: If A task value is “No” @ serial number 4,8 and 499, I need to extract it in summary table under task A.

I have uploaded the sample file for your reference.

Thank you

Regards,

Shan

You want this by vba or formula?

 

[shantraj.antin@gmail.com]

VBA would be great Ratan.

 

[Hui]

Function CountMe(myRng As Range, Optional Check As String = "No") As Variant
Dim tmp As String
Dim i As Integer

tmp = ""
i = 0

For Each c In myRng
  i = i + 1
  If c = Check Then
  If Len(tmp) = 0 Then
  tmp = CStr(i)
  Else
  tmp = tmp + "," + CStr(i)
  End If
  End If
Next

CountMe = tmp

End Function

To use copy the above into a code module in VBA
Then in Excel
=CountMe(B2:B500)
or =CountMe(B2:B500,"No")

=CountMe(C2:C500) or =CountMe(C2:C500,"No")

etc

save as a macro enabled worksheet

See attached

 

[Asheesh]

Hi, Use the below

Option Explicit
Option Compare Text

Sub test()

Dim SearchItem As Range
Dim LastRow As Long
Dim CellCheck As Range
Dim n As Integer
Dim Answer As String


Sheets("Sheet1").Select

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For Each SearchItem In Range("I3:I7")
  
    n = SearchItem.Row - 1
  
    Answer = ""
  
    For Each CellCheck In Range(Cells(3, n), Cells(LastRow, n))

        If CellCheck.Value = "NO" Then
                  
            Answer = Answer & "," & CellCheck.Offset(, -n + 1).Value
      
        End If
      
Next

SearchItem.Offset(, 1).Value = WorksheetFunction.Substitute(Answer, ",", "", 1)

Next

End Sub

EDIT: One more option..

 

[Ratan Bhushan]

Hi PFA

Thanks

 

[shantraj.antin@gmail.com]

Thanks All. Its working