ExcelVBA (Search/ filter/Copy)

[magix]

Hi

I would like to do search, and filter and copy the necessary one into new
worksheet
Please see attached excel file (Sample.xls) for sample data

Column A is Customer Number
Column B is Fruits
Column C is City

I want only those Apple from New York with one condition. If the Customer
also has Orange from New York, then the customer will not be selected.

From the sample data, the output in new worksheet will be

333 Apple New York
555 Apple New York
888 Apple New York

Thank you.

Regards,
Magix

[Dave Peterson]

I don't open attachments...

If I were doing this, I'd put headers in Row 1 (if you don't have them), then in
column D (or a column to the right of your data), I'd put a formula that looked
like:

=b2&"----"&c2
(the "----" is just a separator between the two fields)
Then copy that formula down the range.

After you do that, you can use Ron de Bruin's easyfilter addin:
http://www.rondebruin.nl/easyfilter.htm

or you could take some code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

or

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb



magix wrote:
>
> Hi
>
> I would like to do search, and filter and copy the necessary one into new
> worksheet
> Please see attached excel file (Sample.xls) for sample data
>
> Column A is Customer Number
> Column B is Fruits
> Column C is City
>
> I want only those Apple from New York with one condition. If the Customer
> also has Orange from New York, then the customer will not be selected.
>
> From the sample data, the output in new worksheet will be
>
> 333 Apple New York
> 555 Apple New York
> 888 Apple New York
>
> Thank you.
>
> Regards,
> Magix
>
> Name: sample.xls
> sample.xls Type: Microsoft Excel Worksheet (application/vnd.ms-excel)
> Encoding: x-uuencode

--

Dave Peterson

[magix]

"magix" <[email protected]> wrote in message news:[email protected]...
> Hi
>
> I would like to do search, and filter and copy the necessary one into new
> worksheet
> Please see attached excel file (Sample.xls) for sample data
>
> Column A is Customer Number
> Column B is Fruits
> Column C is City
>
> I want only those Apple from New York with one condition. If the Customer
> also has Orange from New York, then the customer will not be selected.
>
> From the sample data, the output in new worksheet will be
>
> 333 Apple New York
> 555 Apple New York
> 888 Apple New York
>
> Thank you.
>
> Regards,
> Magix
>
>
>
>

To make it simple, let say I have this record:
111 Apple NewYork
111 Banana NewYork
222 Apple NewYork
222 Banana NewYork
222 Orange NewYork
333 Apple NewYork
555 Banana NewYork
555 Orange NewYork
666 Apple NewYork
777 Orange NewYork
777 Apple NewYork

The output I want is:

111 Apple NewYork
333 Apple NewYork
666 Apple NewYork


Note: For Apple in Customer 222 and 777 are NOT selected because this
customer has ORANGE

So, what is the suitable coding to search through multiple rows for same
customer , and do filtering there based on same customer number ?

Thanks.

(Note: assume that this is a huge record, what is best way to archive this
without having long search time ?)

Regards,
Magix

[Jef Gorbach]

Sounds like a case to Data\Filter\Advance, set your criteria using the
dropbox arrows then select-all and copy the resulting visible rows to
another temporary sheet for whatever further processing is required.

"magix" <[email protected]> wrote in message news:[email protected]...
> Hi
>
> I would like to do search, and filter and copy the necessary one into new
> worksheet
> Please see attached excel file (Sample.xls) for sample data
>
> Column A is Customer Number
> Column B is Fruits
> Column C is City
>
> I want only those Apple from New York with one condition. If the Customer
> also has Orange from New York, then the customer will not be selected.
>
> From the sample data, the output in new worksheet will be
>
> 333 Apple New York
> 555 Apple New York
> 888 Apple New York
>
> Thank you.
>
> Regards,
> Magix
>
>
>
>