Excel 2007 : Returning the whole row in new sheet if value in one column match

**pcsx** · 10-20-2009, 06:50 AM

All,

Which function do I use in order to return the content of a whole row, in a new sheet, in case the value in column A match a certain value (in this case "EU1")?

**Palmetto** · 10-20-2009, 07:45 AM

There are a number of ways to go about this, but here is a very basic VLOOKUP example.

Other options include using an combination of INDEX and MATCH, DGET.

If the attached example is not what you meant then post an example workbook with your structure, which shows the desired results.

**pcsx** · 10-20-2009, 08:33 AM

I am not sure about it..

Enclosed you have a short example.

What I want here is that all data (all columns) in Row 3 is returned in Sheet 2 because we have a "region" in column C that equals "EU2".

I basically want all EU2 customers (out of a very long list of different customers/regions) sorted out and placed in a new sheet.

**Palmetto** · 10-20-2009, 10:33 AM

I basically want all EU2 customers (out of a very long list of different customers/regions) sorted out and placed in a new sheet.

Use Advanced Filter with criteria and use the option to copy to a new location.

See this link on using Advanced Filter

You could also use a Pivot Table based on a dynamic named range and set up the region as a page field. Search on Pivot Tables if you are unfamiliar.

**pcsx** · 10-21-2009, 02:49 AM

thanks..

I use the advance filter option now, but since data in updated every third day, I was looking for a more dynamic solution...
I initially thought of Pivot tables, but wanted to know if there was any other solution since the data extracted needs further work and I didn't find that Pivots fittet so easily in.
Based on your suggestions I will however take a look at Pivots once again..

Thank you for your help!