Advanced Filter Issue

MartinS

Active Member
Joined
Jun 17, 2003
Messages
448
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
2,061
Office Version
  1. 365
Platform
  1. Windows
You should check the data to make sure you don't have spaces instead of blanks.
 

MartinS

Active Member
Joined
Jun 17, 2003
Messages
448
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 31, 2015
Messages
2,061
Office Version
  1. 365
Platform
  1. Windows
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
448
Office Version
  1. 365
Platform
  1. Windows

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
448
Office Version
  1. 365
Platform
  1. Windows
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
448
Office Version
  1. 365
Platform
  1. Windows
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
2,061
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,017
Messages
5,834,960
Members
430,330
Latest member
drAli77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top