Results 1 to 11 of 11

Thread: Filter in excel

  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I have about 9000 records are exported from the database in the format that I attached. Now, I need find a way to filter all the ID records with type = 2.

    [attachment=88595:Filter.JPG]

    I could not find a way to do it since the ID and Type are in the different rows.

    Thanks in advance.
    Attached Images Attached Images

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Gary,

    You could write a short VBA program to loop down through the sheet and if Col A is blank replace Cols A & B with the information from the record/row above.

    Other than that I don't see how you could do it.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The easiest way I think, would be to change the way it is exported. The standard export (from Access anyway) would be to export a query and that would repeat the ID for every row the way you want, and put ID in the top row.

    You screenshot shows different formats itself. Is the ID in line with the first row, or above it as in row 2?
    Regards
    John



  4. #4
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, RG.

    Can also provide me the VBA program to deliver what you suggest?

    Thanks in advance.




    Quote Originally Posted by RetiredGeek View Post
    Gary,

    You could write a short VBA program to loop down through the sheet and if Col A is blank replace Cols A & B with the information from the record/row above.

    Other than that I don't see how you could do it.

    RG

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I agree with John, you ought to get the query data if you can, because this is the result of some sort of report.
    But if this is all you can get, you could use the code below, assuming the layout is consistent,
    although rows 1 and 2 look a bit odd.

    I have also included an additional line that will apply a filter for TYPE = 2

    You will need to insert a module into the workbook to apply it.

    Also attached is an example workbook with code in.

    'This will strip out the data as shown into a format that can be filtered.

    Code:
    Option Explicit
    Option Compare Text
    
    Sub ConvertList()
    
    Dim varID, rngCell As Range
    Dim intR As Integer
    
    intR = MsgBox("Are you sure you want to Convert this data list", vbYesNo + vbQuestion + vbDefaultButton1, "Convert List?")
    
    If intR = vbNo Then Exit Sub
    
    'ADD Heading to B1
    Range("B1") = "ID"
    Range("B1").HorizontalAlignment = xlCenter
    
    Set rngCell = Range("C2")
    
    'The Loop will cater for all rows until the last one
    Do Until rngCell.End(xlDown) = ""
        If rngCell = "" And rngCell.Offset(0, -2) = "ID" Then
            'Read ID, then Move and Delete Row (This covers the ODD Layout in example of Row 2
            varID = rngCell.Offset(0, -1)
            Set rngCell = rngCell.Offset(1, 0)
            rngCell.Offset(-1, 0).EntireRow.Delete
        ElseIf rngCell = "" Then
            'Delete Blank Row
            Set rngCell = rngCell.Offset(1, 0)
            rngCell.Offset(-1, 0).EntireRow.Delete
        ElseIf rngCell.Offset(0, -2) = "ID" Then
            'ID in Correct Column So Read it
            varID = rngCell.Offset(0, -1)
            Set rngCell = rngCell.Offset(1, 0)
        Else
            'Need to Write ID into Column B
            rngCell.Offset(0, -1) = varID
            Set rngCell = rngCell.Offset(1, 0)
        End If
    Loop
    
    'On Last Cell so Just write ID to Col B if not an ID only row and not blank entry
    If rngCell <> "" And rngCell.Offset(0, -2) <> "ID" Then rngCell.Offset(0, -1) = varID
    
    'Now Delete Column A
    Range("A1").EntireColumn.Delete
    
    MsgBox "Import List Converted to Filter List", vbExclamation
    
    'If You want to then apply a filter for 2 to Column C you could Un Comment this line
    
    'Un Comment line below to run the ApplyFilter2 Macro
    'ApplyFilter2
    
    End Sub
    
    Sub ApplyFilter2()
    
    Range("A1").AutoFilter Field:=3, Criteria1:=2
    
    End Sub
    [attachment=88605:ConvertListDemo.xls]

    I am sure there are other more elegant solutions!
    Attached Files Attached Files
    Andrew

  6. #6
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much for your help, AKW.

    I just find out the format I have on the report is a little different with what I listed before. I have attached it updated demo. How can I filter column C = 2 and list all Column A for each row? Below is what I want after applying the codes:

    [attachment=88611:4-21-2010 2-03-26 PM.png]


    Below is what I have now:

    [attachment=88612:4-21-2010 2-06-57 PM.png]

    Thanks in advance.

    [quote name='AKW' date='2010-04-21 09:49' timestamp='1271864942' post='836250']
    I agree with John, you ought to get the query data if you can, because this is the result of some sort of report.
    But if this is all you can get, you could use the code below, assuming the layout is consistent,
    although rows 1 and 2 look a bit odd.

    I have also included an additional line that will apply a filter for TYPE = 2

    You will need to insert a module into the workbook to apply it.

    Also attached is an example workbook with code in.
    Attached Images Attached Images
    Attached Files Attached Files

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Let's hope that is the format then.
    Amended Code Routine below covers this..

    Code:
    Sub ConvertList2()
    
    Dim varID, rngCell As Range
    Dim intR As Integer
    
    intR = MsgBox("Are you sure you want to Convert this data list", vbYesNo + vbQuestion + vbDefaultButton1, "Convert List?")
    
    If intR = vbNo Then Exit Sub
    
    'ADD Heading to B1
    Range("A1") = "ID"
    Range("B1") = "NAME"
    Range("C1") = "Type"
    Range("A1:C1").HorizontalAlignment = xlCenter
    
    Set rngCell = Range("A2")
    If Left(rngCell, 2) <> "ID" Then
        MsgBox "Invalid Format Found", vbExclamation
        Exit Sub
    End If
    
    Set rngCell = Range("B2")
    
    'The Loop will cater for all rows until the last one
    Do Until rngCell.End(xlDown) = ""
        If rngCell = "" And Left(rngCell.Offset(0, -1), 2) = "ID" Then
            'Read ID, then Move and Delete Row (This covers the ODD Layout in example of Row 2
            varID = Mid(rngCell.Offset(0, -1), 4)
            Set rngCell = rngCell.Offset(1, 0)
            rngCell.Offset(-1, 0).EntireRow.Delete
        ElseIf rngCell = "" Then
            'Delete Blank Row
            Set rngCell = rngCell.Offset(1, 0)
            rngCell.Offset(-1, 0).EntireRow.Delete
        Else
            'Need to Write ID into Column B
            rngCell.Offset(0, -1) = varID
            Set rngCell = rngCell.Offset(1, 0)
        End If
    Loop
    
    'On Last Cell so Just write ID to Col B if not an ID only row and not blank entry
    If rngCell <> "" And Left(rngCell.Offset(0, -1), 2) <> "ID" Then rngCell.Offset(0, -1) = varID
    
    MsgBox "Import List Converted to Filter List", vbExclamation
    
    'If You want to then apply a filter for 2 to Column C you could Un Comment this line
    
    'ApplyFilter2
    
    End Sub
    See Attached

    [attachment=88613:ConvertListNewLayout.xls]
    Attached Files Attached Files
    Andrew

  8. #8
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much for your help, Andrew.

    But I have the last question regarding this filter request, I have another similar report that have to be filter. The only difference is it has the difference number with name in column A. What code s that I can use to filter it as you did before?

    [attachment=88626:4-22-2010 3-53-44 PM.png]

    Thanks in advance.

    [quote name='AKW' date='2010-04-21 18:06' timestamp='1271894804' post='836347']
    Let's hope that is the format then.
    Amended Code Routine below covers this..
    Attached Images Attached Images

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Are all the names preceded by a space?

    ie Number Space Name?

    Also, are they really numbers, some begin with 0's

    And what do you want in your list?

    Number and Name together
    Number and Name split
    Text Digits as are and Number split?

    Since you are getting what appears to be varied formats
    from a database report that are not easily compatible, can you not request the lists in a suitable format.

    Also, it would be a lot easier if you just posted the spreadsheet rather than just a screen shot.
    Andrew

  10. #10
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, those are the numbers all begins with 0, then name.

    I have uploaded excel file with two separate worksheets, one is the format before the filter, and another one is what I want after filter.

    [attachment=88629:ConvertList_2.xls]

    Thanks again for your help.

    Regards,

    [quote name='AKW' date='2010-04-22 17:32' timestamp='1271979124' post='836615']
    Are all the names preceded by a space?
    Attached Files Attached Files

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    This almost does what you want. It fails since A4 is not blank in your example. If it were blank it would give you want you want.


    Code:
    Option Explicit
    Sub ConvertMe()
      Dim lRow As Long
      
      lRow = Cells(Cells.Rows.Count, 2).End(xlUp).Row
      Columns(1).EntireColumn.Insert
      Range(Range("A2"), Cells(lRow, 1)).FormulaR1C1 = _
    	"=IF(R[1]C[1]=""Process - Completed"",RC[1],IF(RC[1]&RC[2]="""","""",R[-1]C))"
      Columns(1).Copy
      Columns(1).PasteSpecial Paste:=xlValues
      Range(Range("B2"), Cells(lRow, 2)). _
    	SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=+RC[-1]"
      Columns(2).Copy
      Columns(2).PasteSpecial Paste:=xlValues
      Columns(1).Delete
      Range("A2").AutoFilter Field:=1, Criteria1:="="
      Range(Range("A3"), Cells(lRow, 1)).ClearContents
      Selection.AutoFilter
    End Sub
    Steve


Posting Permissions

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