Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Search using formula and get multiple results in rows

  1. #1

    Search using formula and get multiple results in rows



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Click image for larger version. 

Name:	excel.jpg 
Views:	19 
Size:	101.0 KB 
ID:	1938

    I want to search the word "no" from P1 to AD23 and display the results in rows R32,R33,R34 and so on depending upon results. Please help.

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    719
    Articles
    0
    Excel Version
    Excel 2010 64bit
    From the picture you have included, what results do you expect in R32, R33 and R34 ?

  3. #3
    Quote Originally Posted by NoS View Post
    From the picture you have included, what results do you expect in R32, R33 and R34 ?
    I want to get the cell addresses of cells in which word "no" is appearing.

  4. #4
    I want the Cell Address of the cell where word "no" is appearing in these cells:

    R32
    R33
    R34
    R45
    and so on

    For e.g: P5 has "no" so in R32 it should show P5, after that Q4 should appear in R33, than Q8 should appear in R34 ... and so on....
    Last edited by Kamran; 2014-01-03 at 03:38 PM.

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    719
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Kamran, you have posted this question in the Formulas section and I don't know how to do this with a formula, but I'm pretty sure this macro will do it.

    Code:
    Sub GetPositions()
    
        Dim rng As Range
        Dim cel As Range
    
    'range to search
    Set rng = Range("P1:AD23")
    
    For Each cel In rng
        'take variations of no into account
        If UCase(Trim(cel.Value)) = "NO" Then
        'if no is found, put address in next available row in col R    
        Range("R" & Cells(Rows.Count, "R").End(xlUp).Row + 1).Value = cel.Address
        End If
    Next cel
    
    End Sub

  6. #6
    Can you tell me how to apply Macro, I haven't done it before.

  7. #7
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    719
    Articles
    0
    Excel Version
    Excel 2010 64bit

  8. #8

    Excel File

    FORMULA-help.xlsx

    This is the file i am working on.

  9. #9
    Quote Originally Posted by NoS View Post
    This page is not opening

  10. #10
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    719
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Clicking on the link opens it for me.
    If not for you then do a Google search for "contextures adding code to an Excel Workbook"

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •