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]

Could you please post some sample data?

By the way I would recommend deleting your email address from your post unless you like spam.

I could easily post code to get the list of unique suppliers, excluding blanks, and then using that do do something.

What that something would be I just don't know because you haven't explained it very well.

PS What makes you think this will take somebody 15 mins?

The first thing they would need to do would be to create some dummy data, then they would have to reread your posts and try and decipher what you actually want to do.

 

[3xxx]

---

I don't mind spam

it is all right

===

some sample data,, easy

but I don't know How to post it in this board -- so it is difficult, I know pbase though

you could send me an email, then I will reply with some SAMPLE data


it should really take 15 min, the last method seems to work in my mind

but that is NOT A CLEVER Way to do it


===
if I do not receive any constructive reply

I may use LOOPING the whole worksheet - as it seems to be

SIMPLE IN PROGRAMMING

but it will take ages to loop through the whole worksheet, my lecturer will HATE it

thanks (don't want to argue for argue sake though)

 

[XLGibbs]

Re: 15 min -- excel sample sheet required

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

What is a minus query? never heard of it.

It has taken an hour so far, and Norie and I can't as of yet decipher what is actually needed. If it were so simple to figure out, you would have done it by now yourself?

Willing to help, but this is starting to go in circles. Norie---Tag, your it.

 

[3xxx]

this should MORE THAN EXPLICITLY CLEAR>> ( although no real data here, but each column got some blank cells -- this is true)

I want to do it in VBA -- autofilter and advanced filter in the VBA is ACCEPTABLE (avoid writing fromula please, as I don't know how to use VBA to generate FORMULA yet)

the real question is quite simple, but - have to do with autofilter
( autofilter and advanced filter for the same condition is cool, as the result got sequential ROW number, rather than advanced filter only, the rows number become "discontinued'



THE EXCEL EXERCISE in a computing course is about >>>

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 in excel, VBA could go into the hidden rows in autofilter, but I did resolve that, by using BOTH autofilter and advanced filter simultaneously, very NEAT That way)
[@@@] == @@@ indicate column HEADER ROW NAME (with [ ])

LONG STORY SHORT


TASK>>>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


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

any clues HOW TO DO IT (using VISUAL BASIC)

you could use any VBA 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 as a guideline

 

[3xxx]

to make it more CLEAR -- oops, I miss some word

set 1)

if any row of data satify all these 3 criterias (then output >> will be one word "FOUND" in a NEW COLUMN -- the program suppose to add the NEW COLUMN too)


[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

 

[Norie]

3xxx

Is this course work?

If it is we'll probably be able/try to help you but probably only if you ask
specific questions and/or clarify what you want to do .

And we won't, well I won't anyway, give you a complete solution.

XLGibbs

A MINUS query is SQL specific I think, and I don't think it's actually supported in Access, which I mentioned in my first post to this thread.

 

[XLGibbs]

I work primarily in SQL server, C# and various other languages. I don;t follow a MINUS query at all. Maybe it is Oracle or something with slightly different...

either way, homework is homework...I think his lecturer would be more concerned if his student failed to do his assigned work.

 

[Norie]

XLGibbs

I think you might be right about the Oracle bit, but I believe other 'flavours' of SQL also support MINUS queries.

All I know really is that Access doesn't.

 

[XLGibbs]

XLGibbs

I think you might be right about the Oracle bit, but I believe other 'flavours' of SQL also support MINUS queries.

All I know really is that Access doesn't.

Nope, sure doesn't. Neither do MSSQL 2000 or 2005.

MySQL I think might, and I think ORACLE does, but we are drifting off topic a bit. Not that much of this materialized into "on topic".

 

[3xxx]

what !!

even programming with C#

you may realise, you completed only 90% of the task

say in C#, the multi-threading is not as good as others

does it mean that you are not good with C#, if you can't resolve that

== this is a real example, in C#, unlike JAVA and good database, it does not handle multi-user say 300 user who use the same table and change the data simultaneously ==

anyway, I should define and clarify my problm more clear (most people who put a THREAD here, beacuse they have not learn some NEW THING in excel programming yet -- I should not be single out)