Advanced Filter Issue

MartinS

Active Member
Joined
Jun 17, 2003
Messages
417
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
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

Active Member
Joined
Jun 17, 2003
Messages
417

ADVERTISEMENT

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

Active Member
Joined
Jun 17, 2003
Messages
417
If it helps, here is a cut-down of the code to extract one 'block'
Code:
'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

Active Member
Joined
Jun 17, 2003
Messages
417
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

Well-known Member
Joined
May 31, 2015
Messages
1,768
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
NOPQR
1BillingCode1JobCode1JobCode1Charge1Description1
2<><>||

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,914
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top