Previous Topic Next Topic

Excel Search & display query

Closed
Shiny - Apr 16, 2010 at 12:10 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 8, 2011 at 02:23 PM
Hi All,
I am a non IT person.. I have a simple requirement, but dont know how to go about.

The requirement is

My data.
Sno Release Project Contact persons
1 Apr SYL Sam, Mark, Tom
2 May Xim Tom, Frank, Kim
3 June TIG Kim, David, Sam

My requirement is when I search by project or by release,, I need to get all the contact person names.. The same way if I search by contact person name.. E.g. Sam in the above example.. The excel has to show me all the projects that Sam was involved with all the relevant data like Release etc...This has to be displayed in a new sheet in excel.

Is it possible to do in Excel or I need to try something else? Can you help me in doing this?

Related:

5 responses

Answer 1 / 5
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 16, 2010 at 05:32 PM
Assumptions:
1. Name of the sheet where the data is "Sheet1" (correct the code if its not)

2. Name of the sheet where the search result are to be pasted is "Result" (correct the code if its not)

3. Previous search results are to be discarded

4. Data is on 4 columns ( as in sample)


STEPS:

1. Read Assumptions
2. Make a back up
3. Press ALT + F11 at the same time to enter VBE environment
4. Click on "Insert" and add a new module
5. Paste the code ( after the instructions)
6. Run the code

Code: 

Sub SearchData()  

Dim lMaxRows As Long 'max number of rows of data based on cells used on column A  
Dim lFilterRows As Long 'last filtered row  

Dim searchRel As Variant 'what is to be search for Release Info  
Dim searchProj As Variant 'what is to be search for Project Info  
Dim searchPpl As Variant 'what is to be search for Contact Info  

Dim sDataSheet As String 'name of the data sheet  
Dim sResultSheet As String 'name of the result sheet  

    sDataSheet = "Sheet1" 'name of the data sheet  
    sResultSheet = "Result" 'name of the result sheet  
      
    'getting search criteria  
    searchRel = InputBox("What Release you want to search. To skip, just press OK.")  
    searchProj = InputBox("What Project you want to search. To skip, just press OK.")  
    searchPpl = InputBox("Which contact person you want to search. To skip, just press OK.")  
      
    'remove white spaces  
    searchRel = Trim(searchRel)  
    searchProj = Trim(searchProj)  
    searchPpl = Trim(searchPpl)  
      
    ' if all three search criteria are blank then dont do any thing  
    If (Len(searchRel & searchProj & searchPpl) = 0) Then Exit Sub  
      
    On Error Resume Next  
      
        Application.DisplayAlerts = False  
        'delete previous result sheet if it exists  
        Sheets(sResultSheet).Delete  
        Application.DisplayAlerts = True  
          
    On Error GoTo 0  
      
    'adding the result sheet  
    Sheets.Add  
    ActiveSheet.Name = sResultSheet  
      
    Sheets(sDataSheet).Select  
    Cells.Select  
      
    'removing any filter  
    If ActiveSheet.AutoFilterMode Then  
          
        On Error Resume Next  
          
        ActiveSheet.ShowAllData  
          
        On Error GoTo 0  
      
    End If  
      
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row  
      
    If ActiveSheet.AutoFilterMode = False Then  
        Selection.AutoFilter  
    End If  
      
      
    If (searchRel) <> "" Then  
        Selection.AutoFilter Field:=2, Criteria1:="=" & searchRel, Operator:=xlAnd, Criteria2:="<>"  
    End If  
      
    If (searchProj) <> "" Then  
        Selection.AutoFilter Field:=3, Criteria1:="=" & searchProj, Operator:=xlAnd, Criteria2:="<>"  
    End If  
      
    If (searchPpl) <> "" Then  
        Selection.AutoFilter Field:=4, Criteria1:="=*" & searchPpl & "*", Operator:=xlAnd, Criteria2:="<>"  
    End If  
      
    lFilterRows = Cells(Rows.Count, "A").End(xlUp).Row  
      
      
    Range("A1:D" & lFilterRows).Copy  
      
    Sheets(sResultSheet).Select  
      
    Range("A1").Select  
    ActiveSheet.Paste  

    Sheets(sDataSheet).Select 
    Cells.Select 
     
    'removing any filter 
    If ActiveSheet.AutoFilterMode Then 
         
        On Error Resume Next 
         
        ActiveSheet.ShowAllData 
         
        On Error GoTo 0 
     
    End If 

End Sub
Answer 2 / 5
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 16, 2010 at 02:07 PM
How you foresee that you are searching ? Would you be using the excel filters?
Answer 3 / 5
Shiny
Apr 16, 2010 at 03:48 PM
Hi.. When I try to use the excel filters it is not working good for this requirement, since I have many conatct perosn asociated with particular project and these person were also involved in different projects... the filter in excel is not showing all the conatct perosn names for the project.Can you suggest me what should I do.. I dont know any language to code this..Pl help me on this...

Thanks a lotttttt
Answer 4 / 5
Shiny
Apr 19, 2010 at 09:24 AM
Hi ,
Thanks for the Program... But I have one issue on that.. The seacrh result is diaplyaing all the items in the Result page.. instead of required items...

Thanks again a lot for the effort you have taken for me..
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 19, 2010 at 09:32 AM
Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
Shiny
Apr 19, 2010 at 09:30 PM
Hi,

I uploaded a test data file in the below mentioned link...

https://authentification.site/files/22032655/Test_File.xls

Thanks again for helping me with this...
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 19, 2010 at 10:18 PM
Ok, my code had too error as result of copy and paste on my part. I have updated the code up there. These are updated lines

If (searchProj) <> "" Then
Selection.AutoFilter Field:=3, Criteria1:="=" & searchProj, Operator:=xlAnd, Criteria2:="<>"
End If

If (searchPpl) <> "" Then
Selection.AutoFilter Field:=4, Criteria1:="=*" & searchPpl & "*", Operator:=xlAnd, Criteria2:="<>"
End If

However, there is one issue. You have in example above, names separated by comma. How ever in the sample sheet, each name is in individual column, Which one is correct ? It initial was correct, this code should work. If each name is in separate cell, then code needs modification
Shiny
Apr 20, 2010 at 08:41 AM
Ho!! ..the Names are in different cells.. as in the sample sheet... Im terribly sorry for my wrong statement in my above requirement......All the names are in diffenrentc cells...
Answer 5 / 5
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 20, 2010 at 10:27 AM
Assumptions:
1. Name of the sheet where the data is "Sheet1" (correct the code if its not)

2. Name of the sheet where the search result are to be pasted is "Result" (correct the code if its not)

3. Previous search results are to be discarded

4. Data to be filtered exists in column B, C and From E and onward

5. Names to be search can be in any column from E and onward


STEPS:

1. Read Assumptions
2. Make a back up
3. Press ALT + F11 at the same time to enter VBE environment
4. Click on "Insert" and add a new module
5. Paste the code ( after the instructions)
6. Run the code

Code: 
Sub SearchData()

Dim lMaxRows As Long 'max number of rows of data based on cells used on column A
Dim lFilterRows As Long 'last filtered row

Dim searchRel As Variant 'what is to be search for Release Info
Dim searchProj As Variant 'what is to be search for Project Info
Dim searchPpl As Variant 'what is to be search for Contact Info

Dim sDataSheet As String 'name of the data sheet
Dim sResultSheet As String 'name of the result sheet

Dim iMaxCol As Integer 'last column header
Dim iCount As Integer ' last count
Dim sFormula As String ' string for temp formula

    sDataSheet = "Sheet1" 'name of the data sheet
    sResultSheet = "Result" 'name of the result sheet
      
    'getting search criteria
    searchRel = InputBox("What Release you want to search. To skip, just press OK.")
    searchProj = InputBox("What Project you want to search. To skip, just press OK.")
    searchPpl = InputBox("Which contact person you want to search. To skip, just press OK.")
      
    'remove white spaces
    searchRel = Trim(searchRel)
    searchProj = Trim(searchProj)
    searchPpl = Trim(searchPpl)
      
    ' if all three search criteria are blank then dont do any thing
    If (Len(searchRel & searchProj & searchPpl) = 0) Then Exit Sub
      
    On Error Resume Next
      
        Application.DisplayAlerts = False
        'delete previous result sheet if it exists
        Sheets(sResultSheet).Delete
        Application.DisplayAlerts = True
          
    On Error GoTo 0
      
    'adding the result sheet
    Sheets.Add
    ActiveSheet.Name = sResultSheet
      
    Sheets(sDataSheet).Select
    Cells.Select
      
    'removing any filter
    If ActiveSheet.AutoFilterMode Then
          
        On Error Resume Next
          
        ActiveSheet.ShowAllData
        ActiveSheet.AutoFilterMode = False
          
        On Error GoTo 0
      
    End If
      
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    iMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
        
    For iCount = 5 To iMaxCol
        sFormula = sFormula & " & RC" & iCount & " & ""|"""
    Next

    If (sFormula <> "") Then sFormula = """|""" & sFormula
    
    Range(Cells(1, iMaxCol), Cells(lMaxRows, iMaxCol)).FormulaR1C1 = "=" & sFormula
    
    If ActiveSheet.AutoFilterMode = False Then
        Selection.AutoFilter
    End If
      
    If (searchPpl) <> "" Then
        Selection.AutoFilter Field:=iMaxCol, Criteria1:="=*|" & searchPpl & "|*", Operator:=xlAnd, Criteria2:="<>"
    End If
    
    If (searchRel) <> "" Then
        Selection.AutoFilter Field:=2, Criteria1:="=" & searchRel, Operator:=xlAnd, Criteria2:="<>"
    End If
      
    If (searchProj) <> "" Then
        Selection.AutoFilter Field:=3, Criteria1:="=" & searchProj, Operator:=xlAnd, Criteria2:="<>"
    End If
      
      
    lFilterRows = Cells(Rows.Count, "A").End(xlUp).Row
      
      
    Range("A1:D" & lFilterRows).Copy
      
    Sheets(sResultSheet).Select
      
    Range("A1").Select
    ActiveSheet.Paste

    Sheets(sDataSheet).Select
    Cells.Select
     
    'removing any filter
    If ActiveSheet.AutoFilterMode Then
         
        On Error Resume Next
         
        ActiveSheet.ShowAllData
        ActiveSheet.AutoFilterMode = False
        
        On Error GoTo 0
     
    End If
    Range(Cells(1, iMaxCol), Cells(lMaxRows, iMaxCol)).Clear
    Range("A2").Select
    
End Sub
rahul
Feb 3, 2011 at 06:28 AM
hello sir
can u help me related with this topic.as I want to search 4 sheets and want to display results on different sheet
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 8, 2011 at 02:23 PM
Rahul, you would have to loop thru each sheet to get the results

basically, from this point
Sheets(sDataSheet).Select
Cells.Select
you have to repeat for each sheet

if you are not strong on code side, then most easy way would be to have the result for each sheet go to a separate sheet and then have a code to combine the results into one master sheet.

Similar discussions

Ghost mail
WondrousCrocodile61 -
HelpiOS -

1 response