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

 

[MartinS]

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​

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