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]

What's the actual question?

Do you want all records where Supplier is blank, or is it the opposite?

PS As far as I'm aware you can't do MINUS queries in Access, you need to use a different method.

 

[3xxx]

my question is --- advanced filter.. .....

advanced filter

the usual way to use it

is supplier contain DHL

then we filter all the DHL ones out (all rows will have DHL)

---

How about, using advanced filter

I want the rows showing UPS , FEDEX, TNT to be in the result (don't want one row of DHL)

is that possible ???


( this could be an ADVANCED Question)


== (if this cannot be done, actually , I am trying to do something simple , about auto-filter and visible rows, but I want to POST THIS HARD question for the experts here--- FIRST, if ti can be done, my program is more simple to CODE it [rather than autofiler alone] -- I bet !)

 

[XLGibbs]

This is easy, but your question is cryptic.

There are things possible, but what have you actually tried?

AutoFilter can be set to filter where rows = multiple items.

In Auto-Filter you can select the column and choose custom, and the options are pretty self explanatory.

You can also simply choose Filter = "DHL" or <> "DHL"

 

[Norie]

Sorry I still don't follow.

Could you post sample data with expected results?

Do you want to return all the DHL, UPS, FEDEX and TNT records?

Or is it all the UPS, FEDEX and TNT but not the DHL records?

If so this is possible using both autofilter and advanced filter.

 

[3xxx]

advanced filter

autofilter -- I know

but just figure out how to >>

processing only VISIBLE ROW
-- all I have to do is simple >> just put the word "FOUND" in a new empty column ( not the hidden rows filtered by autofilter)

====================


another Question

Advanced filter

say

A with suppliers
sample of A will be

= DHL
= UPS
= FEDEX
= "blank cell" -- we have not got any supplier decision yet

=== my Q is

all I want to do

is to choose the Supplier than is NON-blank cells
i.e. every rows with Supplier will be shown
after advancedfilter, every row with blank cell in column A will not be shown


am I too dreamy to do that with advanced filter??


actually, after advanced filter
I want to put the word "FOUND" in those advancedfilter ROW

===

so either way (with or without ADVANCED FILTER) could achieve my expected result !!


thanks for keep on reading !!

 

[the magician]

Sub TryThis()
Dim PostInEnglish
PostInEnglish = UseSentences + ProperGrammar - CrypticNotation
    If PostInEnglish = True Then
        HelpForthcoming = Yes
    Else
        ConfusionAbounds > 0
    Endif
End Sub

 

[Norie]

Sorry that still doesn't make sense, to me anyway.

If you just want to show rows with a Supplier use autofilter and select Non-Blanks from the dropdown.

 

[3xxx]

el mago
Know the rules
Use Code Tags
Don’t KISS me

AS QUOTE

sorry, I won't kiss you with that ANSWER , hehhehe, definitely

 

[3xxx]

ya, but I want to do it in VBA

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

there are 3 set of criterias, I want to program the EXCEL and put the word FOUND (using VBA programming , as VBA navigation may be different from the usual human with mouse navigation, VBA could go into the hidden rows in autofilter, and I did resolve that, by using BOTH autofilter and advanced filter simultaneously)
[@@@] == @@@ indicate column HEADER ROW NAME (with [ ])

LONG STORY SHORT

the 3 set of criteria, that I want to put the word "found" in Column AA

set 1)

[supplier code] got something there (i.e. non blank)
[Active supplier] is NO
[month] = NOV
OUTPUT>>> for all rows satisify this coniditon -- set 1
>>>>>> >>put the word "found" in Column AA


set 2)
[supplier code] got something there (i.e. non blank)

[Active supplier] is NO
[Month] JAN to OCT
[DISUSE] = "Yes"


OUTPUT>>> for all rows satisify this coniditon -set 2
>>>>>> >>put the word "found" in Column AA

set 3)
[Commodity] is BLANK
[Active supplier] is NO
[Month] is anything but not NOV
[online Tracking] is "Available"

OUTPUT>>> for all rows satisify this coniditon -set 3
>>>>>> >>put the word "found" in Column AA


=== >>> ^..^ ===========

is it very clear now, any clues HOW TO DO IT

you could use every method -- except looping the whole worksheet,20K rows in total !!

see whether you can come up with PROGRAM CODING That do similar thing>> should able to use radar.com key in >> autofilter sort site:mrexcel.com AND we should get some previous forum thread