Looping Subject to a Condition

[andrewc]

Hi,

In A1:A1000 I have a list of product names; Product A, Product B etc, each appearing more than once. I would like to create a simple macro that extracts from column A, and puts into a new range starting at C1, each of the names of the products. So C1 would contain the text "Product A", C2 would contain the text "Product B" etc until column C contains the list of product names from A1:A1000.

In a previous thread someone kindly suggested the following code which works perfectly:

Please Login or Register  to view this content.

I would now like to amend this code so that it only returns to column C those names from column A that have the word “Confirmed” in the adjacent cell in column B.

Can someone please suggest a VBA solution?

Thanks!

[AB33]

What you need is auto filter. You can do it yourself using filter function. Just filter column A by “Confirmed” and copy the filtered cells in to column B.

[Andy Pope]

You can use the Criteria argument to further filter the data.

Please Login or Register  to view this content.

Assumes cells J1 and J2 are empty and will temporarily be used to store criteria.
column B has a header of Status

[Debraj Roy]

In case if you are looking only to copy Column A..

you can follow snb's technique for unique with criteria..

Please Login or Register  to view this content.

[Fotis1991]

@ andrewc

We are waiting from a long time member to knows the forum rules. Pls use code tags for your codes.

This time and only this i did it for you. I hope that will not need again to say something like this.

Pls, you the old members, do Moderators life a little easier!

[andrewc]

Apologies for the lack of code tags - while I've used this forum a quite a lot I don't think that I've ever posted VBA code and wasn't aware of the need to use tags.

I'll be sure to use tags from now on!

[Fotis1991]

Your understanding is really appreciated!

[andrewc]

Thank you all for the suggestions

[AB33]

Fotis,
I did not know you are a forum moderator. I am always curious why the other sites such as Cage Code do not ask for code tags, the code is auto wrapped in tags?

[SoulPrisoner]

Hi andrewc,

if i may suggest I´d use just a function:

=if("target cell" = "comfirmed";A#;"")

and copy it all the way down (instead of target cell use the address where comfirmed should ocur; instead of # use the number of the row wher you´re placing the function.

I feel like this should do the trick if i understood your issue correctly, or you still want to have uniq selection in column C? if so I´d suggest advanced filtering

[Fotis1991]

Fotis,
I did not know you are a forum moderator. I am always curious why the other sites such as Cage Code do not ask for code tags, the code is auto wrapped in tags?

I am since yesterday! New one!

http://www.excelforum.com/the-water-...94#post3401394

I don't know why other forums don't use these. How i could i?

Our forum rules reqired code tags arount the code and as Moderator i am here trying to keep the rules alive!

[AB33]

Fotis,
"I don't know why other forums don't use these"

They do, but the OP has not required to use code tags-That is the whole point. When one posts their code, the code appeared with code tags auto-wrapped-no need for human intervention.

[Fotis1991]

I am not the one that can answer to this. I believe that you can understand this.

And as this thread is not the right place to duscuss it, pls make your question in Suggestion for Improvment, sub forum. Perhaps Admins can offer you an answer.