how to make a filtered list on a different sheet

[Guest]

Hi there was an answer to my question in this post :

GET UNIQUE ROWS FROM ONE PAGE TO ANOTHER 12/18/2005 10:20 PM PST

but unfortunately the answers were in links that are no longer valid

so here goes...

I have a master list of data.
I want to copy the entire row of data to a new sheet if the data in a
certain column matches my criteria. There will be multiple rows that meet the
criteria. and I will be wanting to place the columns in a different order....

Eg:
MasterSheet

Referral Source Consultant Inquiry Result Adviser Name

John Sam MIC Appoint Mike
John Paul Discuss SOA Sarah
Carl Sam Question SOA Mike
John Fred MIC SOA Mike
Homer Sam Plan Appoint Sam

Resultant Sheet ( required/wanted outcome)
This sheet/List should only be populated with rows where Result = Appoint
and the columns will be in a different order.....

Result Adviser Name Referral Source Consultant Inquiry
Appoint Mike John Sam MIC

Appoint Sam Homer Sam Plan


I am hopeing that the answer is a cell array in the Resultant sheet, so that
I can arrange/exlcude columns as I need...

Thanks in advance
Mark

 

[Guest]

The easy way is create a Pivot table

 

[Guest]

Thanks, I don't like pivot tables ( probably trough lack of using them)

I found this link..
http://www.rondebruin.nl/copy5.htm

so have ripped it apart and use VB scripting / macros instead... thanks
though.

That said , If anyone has a way to do it without macros, or pivot tables ,
I'd much prefer that as the user of the file is not that good at excel and
I'd prefer to automate everything for her...

 

[Guest]

.. If anyone has a way to do it without macros, or pivot tables ,
I'd much prefer that as the user of the file is not that good at excel and
I'd prefer to automate everything for her... ....
.. I am hoping that the answer is a cell array in the Resultant sheet,
so that I can arrange/exclude columns as I need...

You can have both the automation plus the flexibility to configure the
sequence of col headers / omissions using a simple/fast non-array set-up as
well (it takes only a few seconds to set up) ..

Illustrated in this sample construct:
http://www.savefile.com/files/992395
AutoFilter on diff sht by flexible col header.xls

Source table assumed in sheet: M, cols A to E,
data from row2 down, with key col = col D

In result sheet: X,

Paste the col headers from M in the desired sequence within B1:E1,
viz: Adviser Name, Referral Source, Consultant, Inquiry

Then in A2:
=IF(M!D2="Appoint",ROW(),"")
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(OFFSET(M!$A:$A,,MATCH(B$1,M!$1:$1,0)-1),SMALL($A:$A,ROWS($1:1))))
Copy B2 to E2. Select A2:E2, copy down to cover the max expected extent of
data in M, eg down to E100. Hide away col A. Incredible, but that's it. The
required filtered results will appear in cols B to E, all neatly bunched at
the top.

---

 

[Guest]

Hi,

To understand how to use Advanced Filter, refer to topic "Filter by using
advanced criteria" in the Microsoft Excel 2003 online help and then complete
the following steps.

Do the following:

1. On the Data menu, point to Filter and then click Advanced Filter.
The Advanced Filter dialog box appears.
2. Under Action, click the Copy to another location option.
3. In the List range text, click and then select the range.
4. In the Critera range text box, click and then select the crtirea range.
5. In the Copy to text box, click Sheet2 -worksheet-tab, and select the new
location.
6. If you want to extract only unique records from the list, then select the
Unique Records only check box or retain the default setting.
7. Click OK to save the settings and to return to the worksheet.

Challa Prabhu