Advanced Filter Issue

MartinS

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

Thanks Jeffrey - I knew you could use multiple rows but hadn't ever seen multiple columns of the same name. Good to know. I think that's probably the way to go.
Thanks again
Martin
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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
Back
Top