Previous Topic Next Topic

Excel issue - how to return data from a list

Closed
Katie Watkins - May 14, 2010 at 05:55 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 14, 2010 at 08:57 PM
Hello,

I'm using Excel 2007 and I simply can't do what I need to and can't find an answer anywhere!

I have a large table of approx 500,000 lines of data (in a couple of separate sheets!). I also have a list of machine names that I want the data for from that table.

What I'd like to do is to have a formula/macro that looks for the machine name in the large table and returns all the data from the rows where there is a match found...

UPDATE: I have a list of machine ID's and a very large table containing those Machine ID's and others not needed. Each row then has a lot of information that is needed. I would like to only pull our the rows that match the machine ID's in my list, so that I can see all the data in the rows but just for those machines

Have large data table ie

Machine name Software installed IP address user id
GB234W Microsoft Word 10.10.10.1 kwatkins
GB234W Microsoft Excel 10.10.10.1 kwatkins
GB235W Microsoft Word 10.10.10.2 jsmith

Now in my list of machine ID's, I have GB234W listed, but not GB235W, so I only want to see the rows containing data on that machine...

Can anyone please help?

Thanks
Katie

2 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 14, 2010 at 06:01 AM
There is no formula that can do it. You have to have a macro to do that.

Are you looking for some sort of search feature, where on a sheet you enter a machine id and you get a sheet which has all the matches from all the sheets?
0
Katie Watkins
May 14, 2010 at 06:04 AM
Right, not used macros for many, many years. I have a list of machine ID's and a very large table containing those Machine ID's and others not needed. Each row then has a lot of information that is needed. I would like to only pull our the rows that match the machine ID's in my list, so that I can see all the data in the rows but just for those machines
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 14, 2010 at 06:06 AM
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ etc and post back here the link to allow better understanding of how it is now and how you foresee.
0
Katie Watkins
May 14, 2010 at 06:11 AM
My firewall won't let me get to either of those sites. Will try to explain here.

Have large data table ie

Machine name Software installed IP address user id
GB234W Microsoft Word 10.10.10.1 kwatkins
GB234W Microsoft Excel 10.10.10.1 kwatkins
GB235W Microsoft Word 10.10.10.2 jsmith

Now in my list of machine ID's, I have GB234W listed, but not GB235W, so I only want to see the rows containing data on that machine...

Does that help?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 14, 2010 at 07:04 AM
So each machine would appear once in data table and what you want is against each machine id present in the list, copy the information from the table next to the matched machine id on the list ?
0
Katie Watkins
May 14, 2010 at 07:06 AM
There would probably be multiple lines of data against the machine names I need, but apart from that. Yes
0
Answer 2 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 14, 2010 at 08:57 PM
Try this


Assumption:

1. The list of machines are on a sheet titled "Search List" and in column A

2. The search results will be posted on a sheet titled "Search Result"

3. The headers are on row 1

4. The search results are refreshed each time and previous search are deleted. 




Sub SearchResult() 
Dim sSrchLstSheet As String 
Dim lSrchLstStartRow As Long 
Dim lSrchLstEndRow As Long 
Dim Sheet As Object 
Dim sSearchFor As String 
Dim sResultSheet As String 
Dim lRsltSheetRow As Long 
Dim bScreenUpdating As Boolean 'current status of screen updating 
Dim bDisplayAlerts As Boolean ' show show warnings 
Dim iFilterCol As Integer 

    bScreenUpdating = Application.ScreenUpdating 
    bDisplayAlerts = Application.DisplayAlerts 


    Application.ScreenUpdating = False 

    On Error GoTo Error_Handle 
     
    sSrchLstSheet = "Search List" 
    lSrchLstStartRow = 1 
    sResultSheet = "Search Result" 
    iFilterCol = 1 
     
    lSrchLstEndRow = Sheets(sSrchLstSheet).Cells(Rows.Count, "A").End(xlUp).Row 
         
    On Error Resume Next 
        If bDisplayAlerts Then Application.DisplayAlerts = False 
        Sheets(sResultSheet).Delete 
        If bDisplayAlerts Then Application.DisplayAlerts = True 
    On Error GoTo Error_Handle 
     
    Sheets.Add 
    ActiveSheet.Name = sResultSheet 
    lRsltSheetRow = 0 
     
    Do While (lSrchLstStartRow <= lSrchLstEndRow) 
         
        sSearchFor = Sheets(sSrchLstSheet).Cells(lSrchLstStartRow, "A") 
         
        For Each Sheet In Sheets 
         
            If (Sheet.Name = sSrchLstSheet) Then GoTo Next_Sheet 
            If (Sheet.Name = sResultSheet) Then GoTo Next_Sheet 
             
            If (Sheet.Cells(1, Columns.Count).End(xlToLeft).Column = 1) Then 
                If Sheet.Cells(1, 1) = "" Then GoTo Next_Sheet 
            End If 
             
            If (lRsltSheetRow = 0) Then 
                 
                Sheets(sResultSheet).Range("1:1") = Sheet.Range("1:1").Value 
                lRsltSheetRow = 2 
             
            End If 
             
            Sheet.Select 
            Cells.Select 
            If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False 
             
            If ActiveSheet.AutoFilterMode = False Then 
                Selection.AutoFilter 
            End If 

            Selection.AutoFilter Field:=iFilterCol, Criteria1:="=" & sSearchFor, Operator:=xlAnd, Criteria2:="<>" 
             
            lastrow = Cells(Rows.Count, iFilterCol).End(xlUp).Row 
            If (lastrow > 1) Then 
             
                Rows(2 & ":" & lastrow).Copy 
                Sheets(sResultSheet).Range("A" & lRsltSheetRow).PasteSpecial 
                lRsltSheetRow = Sheets(sResultSheet).Cells(Rows.Count, iFilterCol).End(xlUp).Row + 1

            End If 
             
            If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False 

Next_Sheet: 
        Next Sheet 
         
        lSrchLstStartRow = lSrchLstStartRow + 1 
     
    Loop 
     
    GoTo End_Sub 
Error_Handle: 
     
    MsgBox Err.Description 
     
End_Sub: 

    Application.ScreenUpdating = bScreenUpdating 
    Application.DisplayAlerts = bDisplayAlerts 
     
End Sub
0

Similar discussions

Facebook Messenger - Suggested Friends
jadess_0462 -
Akajackal -

2 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses