Advance Filter - Formula Alternative

[scottuk]

Hi, I am using a table below that contains a large amount of data; approx. 1800 unique rows, that will grow to 3500 potentially. I have various "Groups" within the data and want to create a separate workbook containing just 1 of the data groups. I have tried advance filters and IF calculations but cannot find a solution to creating the dynamic table in the new workbook. Any thoughts would be greatly received. I am not a VBA user (yet) and so straight formula would be my preferred solution method ? I would like the second workbook to have the same layout but only take across the rows that contain the word "corporate" - however, the "Code" is the most important as its the one used to VLOOKUP the data contained on each row - how do I get the "Corporate" rows across to the new sheet? thanks all in advance! Scott [TABLE="width: 500"]
<tbody>[TR]
[TD]Code
[/TD]
[TD]Name
[/TD]
[TD]Type
[/TD]
[TD]Group
[/TD]
[/TR]
[TR]
[TD]IDA11
[/TD]
[TD]Test 1
[/TD]
[TD]KNR
[/TD]
[TD]Corporate
[/TD]
[/TR]
[TR]
[TD]ILQ33
[/TD]
[TD]Test 2
[/TD]
[TD]LNR
[/TD]
[TD]Corporate
[/TD]
[/TR]
[TR]
[TD]IVDDI
[/TD]
[TD]Test 3
[/TD]
[TD]LNR
[/TD]
[TD]Corporate
[/TD]
[/TR]
[TR]
[TD]ILPLE
[/TD]
[TD]Test 4
[/TD]
[TD]Wholesale
[/TD]
[TD]Wholesale
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Maybe this can help
Excel Advanced Filter Introduction

See the topic
Extract Data to Another Worksheet

M.

 

[Jerry Sullivan]

Hi all,

One note to add to Marcelo's suggestion....Even though the instructions describe how to use Advanced Filter to another Worksheet in the same workbook, you can use the same process to copy to another Workbook.

You mention wanting this to be a dynamic table in the new workbook, and one drawback of AdvancedFilter is that you would need to repeat the steps each time your source data changes (which you could automate through VBA).

Peter_SS has a nice formula approach in post #22 of this thread....
http://www.mrexcel.com/forum/excel-...a-return-multiple-rows-excel.html#post3421024


If you have large data sets, you might consider using a query table instead since the formulas could slow down your calculations.

First save and close your workbook with the source data
Then open the workbook in which you want the data to be imported.

From the Ribbon (for xl2007 and later versions):
Data> From Other Sources > From Microsoft Query

In the Choose Data Source dialog:
Excel Files* > OK > (Browse to your file and select it) > OK

In the Query Wizard - Choose Columns:
Using the arrow buttons, Add the 4 columns you want to import
Use up/down arrows to put the columns in the desired order
Next >

In the Query Wizard - Filter Data:
Column to filter > Click on Group
Code > Pick "equals" and "Corporate"

In the Query Wizard - Sort Order:
Next >

In the Query Wizard - Finish:
Click option: Return the Data to Excel
Finish

In the Import Data dialog
Click options: Table, New Worksheet
OK

That should import your table. Once this is setup, your table just needs to be refreshed to import the latest data.

 

[scottuk]

JS411 - Thank you so much - the Query option worked a treat - first time I've ever used this function... no doubt I will be using this more often. The fact that you can set it to refresh each time you open the file is perfect!!