ADVANCE-FILTER -- quite difficult :-> need some exp

[3xxx]

:->

Hi, got something cool I would like to achieve

if it is in ACCESS, so simple, just MINUS QUERY


====
but in EXCEL, need your ASSISTANCE

about VBA program with advanced filter
-- just what advanced filter code look like >>> (not really relevant to this Q)

Range(LowestColumnAlphabet & "5:" & HighestPlusPlusPlus & FinalRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("AS1:AS2"), CopyToRange:=Range("C" & LowestColumnAlphabet & "5:C" & HighestPlusPlusPlus & "5 "), Unique:=False



============ say, I would like to use advance filter or with some other method (can't use autofilter though-- the limitation, could use looping but this is inefficient last resort )



THE QUESTION >>>

Advanced FILTER
-- say [Supplier ] column those NON-blank one



==== if it is access, just do a query for (those with value ), then do a minus query >> ALL - (those with value)



just wonder, any method (want to avoid autofilter as it can't paste the value in sequenced ROWS later after using autofilter)


welcome any suggestion, welcome any print-screen examples



many thanks

 

[Norie]

I'm sorry but it's not 'very clear now'.

In fact I don't quite see where filters come into it.

Couldn't you just put formulas in column AA to get the results?

PS Please provide some sample data.

 

[the magician]

the real question is more complex than what I stated (in the past)

pucker up!

 

[XLGibbs]

You don't need VBA to put the word FOund in a column. You can use a formula in column AA.

 

[3xxx]

any method>>

it mean programming method

or use VBA program to put some formula there -- but formulae are highly undersirable -- as the program is VERY CLEVER

the program could response to dymanic column increase, one more column every week

so, better to avoid any formulae within any cells


===

Any other suggestion

I may repost my question, as it got to the second page, and I have not got an answer that I may apply to my big excel program, about 200 lines (not very big indeed, hehe he)

thanks for trying

 

[XLGibbs]

Of course, how silly of me to suggest built in functionality which is faster and easier than.

A formula solution would be truly clever. Can't help you with the programming....can't do much with cryptic-at best-information provided.

 

[Norie]

You seriously need to explain what you want in more simple and coherent terms.

We could easily start throwing code at you but I for one don't see the point when it's not clear what you actually want to do.

And you still haven't posted any example data.

PS we are trying to help but we're not mind readers, though we do have a magician.

 

[3xxx]

email ?? wanna to try few excel data ??

if you are confident with excel programming (do not use any formla at all) -- say you got spare 1 hour to try to do it


I could extract part of an actual file -- for you to try out

if interested, please leave your email in this thread !!

will respond in 10 min, if you do so !!

the main program is by running module 2 and you can add something at the very end (before END SUB) and mail back to me !!

this way, it will be unambiguous and not vague

thanks

 

[XLGibbs]

Remember you are asking for free help here. From very experienced people that are used to varying solutions. Dismissing suggestions that are valid form people clearly far more experienced as you, would seem counterproductive to actually solving the problem.

I know I don't have an hour to spend on this one.

 

[Norie]

If this is for some sort of computer course as you've indicated in your unneccessary new thread then perhaps you should consider spending an hour on it yourself.

I'm pretty sure we would be glad you help if you actually clarified what you want to do and asked specific questions.

But nobody is going to do the whole thing for you.

 

[3xxx]

15 min -- excel sample sheet required

how about this way, this is same as MINUS QUERY

let say

we have a ORIGNAL SET OF data

===> we use advanced filter to filter out all the rows that is non-blank in [Supplier]

and we use unique to find out unique set of suppliers say DHL, FEDEX etc called this SUPPLIERbigLIST

===> then we loop thro' this supplierBIGlist

and final OUTPUT will be DHL -- row 1 , 2, 3, 4
then FEDEX row 5, 6
then ..... row 7,8.9,10

and we add one column there
all this ROWS (with non-blank cells in column >> supplier
we put the word "FOUND" by adding a new column, called this column [SUPPLIERfound]

is it possible??

may email your sample to HKandrew@msn.com -- then in effect, this is minus query using advanced filter -- should take you 15 min !!