Filter in excel

Topic

New Reply

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.

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

Thanks in advance.

Reply | Quote

Replies

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!
Computer Specs

Reply | Quote

Thanks, RG.

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

Thanks in advance.

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

Reply | Quote

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?

Reply | Quote

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

I am sure there are other more elegant solutions!

Reply | Quote

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:

Below is what I have now:

Thanks in advance.

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.

Reply | Quote

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

Reply | Quote

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?

Thanks in advance.

Let’s hope that is the format then.
Amended Code Routine below covers this..

Reply | Quote

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.

Reply | Quote

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.

Thanks again for your help.

Regards,

Are all the names preceded by a space?

Reply | Quote

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

Reply | Quote