Auto-build lists into new worksheet

[RichardH]

Scoured forum and couldn't find answer to this.

Sheet 1 ("Dump") is a raw dump of data relating to survey results from a number of different cities. I want to read through this dump, isolate any rows where Location="Glasgow" and copy them across into a new list on an existing sheet ("Report") - replacing any existing data on that sheet.

Although I can manually delete "Report" cells, filter "Dump" sheet and then copy and paste I am looking for some way to do this automatically as the data dump is updated daily by a manual copy and paste operation. I cannot filter the data at source as i will require comparison to other locations elsewhere in the workbook and need all the data present in "Dump"

Tried a straight =IF(DUMP!A1="Glasgow","DUMP!A1","") but this leaves obvious gaps that mess up later calculations based on the data.

Have tried VLOOKUP but this obviously only returns first survey. Do not see how INDEX/MATCH can perform this either.

Can anyone point me in right direction here pls?

[Gary Brown]

Try using Data | New database query

Create a data source name and use the Microsoft Excel Driver

Select Connect and then click on the select workbook (i.e. your raw dump) and select OK.

Then select the data source name you created above and it will either take you to the query wizard or into Microsoft Query.

Add a table, i.e. the sheet name with your data in, and add all of the fields to the query. Apply a filter on the field with Glasgow in to restrict records to Glasgow only and the Exit and return the data to Excel.

This will then permanently link this file to your source file, if the source file changes, you simply refresh the query to give your new results (Data | Refresh Query)

Good Luck

Gary

[davesexcel]

Hi,
Check out this example,
Sheet2 has the data, sheet3 has the item to find, it is macro driven
http://www.excelforum.com/attachment...8&d=1201836478