Advanced Filter Issue

[MartinS]

Hi
So I'm having some issues using Excel's advanced filter functionality in that it's sometimes including records it shouldn't.

Lets say the data is as follws - 10 columns of random data, followed by 12 blocks of four columns titled BillingCode1,JobCode1,Charge1,Description1 to BillingCode12,JobCode12,Charge12,Description12. Each of these are formulae which return either text or "". So there may be text for the columns BillingCode1, JobCode1, Charge1, Description1 for a row, but it could be blank for all other columns, i.e. BillingCode2, JobCode2, Charge2, Description2...BillingCode12, JobCode12, Charge12, Description12, and equally, it could be blank for the columns BillingCode1, JobCode1, Charge1, Description1, but present for the columns BillingCode2, JobCode2, Charge2, Description2 and so on.

Above the data is a criteria range covering the same number of columns.

To the right of the data is an output range with a small number of columns.

The first row of the data range contains unique text to identify each column, and this is reflected in the criteria range, and then a small sample of columns are reflected in the output range.

So, I've got a loop that runs from 1 to 12
Starting in, say, AD3 (the cell below the text JobCode1 in the criteria range), the code writes <>""
The output range has the relevant column headings set to BillingCode1,JobCode1,Charge1,Description1
The data, criteria ranges and output ranges are selected and the extract run (unique records only).
It then clears the criteria in AD3

For the next repitition, the range is shifted 4 columns along (AH3), the output range has the relevant column headings set to BillingCode2,JobCode2,Charge2,Description2
The data, criteria ranges and output ranges are selected and the extract run (unique records only).
It then clears the criteria in AH3

This repeats for the final 10 iterations.

So I'm trying to extract the data for each 'block' where the column 'JobCodeN' is not blank.

My issue is that for some of the iterations, I get data where the JobCode column is = blank for one record, which to my thinking should be ignored? Or am I misunderstanding how the extract criteria works?

There is definitely no other data in the criteria range (I have tested the process manually and get the same results).

Many thanks in advance

Martin

 

[Jeffrey Mahoney]

Try using <> only, Not <>""

 

[MartinS]

Try using <> only, Not <>""

Thanks, but same result.

 

[Jeffrey Mahoney]

You should check the data to make sure you don't have spaces instead of blanks.

 

[MartinS]

You should check the data to make sure you don't have spaces instead of blanks.

I've done that, definitely it's either a string or "", nothing else

 

[Jeffrey Mahoney]

When I manually run an advanced filter to copy unique records, the original method you provided didn't work. The <> does. I don't have any other suggestions.

 

[MartinS]

When I manually run an advanced filter to copy unique records, the original method you provided didn't work. The <> does. I don't have any other suggestions.

Thats why it's stumped me. If I run it with <>"" or <>, I get exactly the same results, i.e. one record where the JobCode field is an empty string. It should work as it's set up,so I'm at a loss as to why it doesn't.
Thanks for the sugestions though, appreciated
Regards
Martin

 

[MartinS]

If it helps, here is a cut-down of the code to extract one 'block'

'Declare and intialise variables
Dim m_oCriteriaCell     As Object
Dim m_sCriteriaAddress  As String: m_sCriteriaAddress = "AY3"
    'Create initial range object
    Set m_oCriteriaCell = Sheets("Calcs").Range(m_sCriteriaAddress)
    'Set criteria...
    m_oCriteriaCell.Value = "<>"
    'Set sheet association
    With Sheets("ExportData")
    'Set appropriate output columns
        .Range("F2") = "JobCode4"
        .Range("K2") = "Charge4"
        .Range("M2") = "Description4"
    'End sheet association
    End With
    'Extract the data
    Range("ChargingData").AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Range("Calcs!ChargingCriteria"), _
            CopyToRange:=Range("ExportData!ChargingExtract"), _
            Unique:=True

 

[MartinS]

Hmm I just found that if the cells are actually blank, then it works as expected, but when the cell is a blank empty string defined by a formula, it doesn't.

I simply copy/pasted as values the data, criter and output ranges to another workbook, cleared the contents for all cells in the data range where the value resulted as "", and then re-ran and it worked as expected.

So it's to do with an empty string as opposed to a blank cell.

 

[Jeffrey Mahoney]

Maybe you could result the formula as a unique character or characters instead of NUL or blank. Did you know you can have repeat headers in your criteria that act as AND criteria. So if your formula resulted in "||" then you could have both criteria met

Excel 2013/2016

	N	O	P	Q	R
BillingCode1	JobCode1	JobCode1	Charge1	Description1
<>	<>||

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​