Extract records based on criteria

**Amarjeet Singh** · 12-23-2008, 03:52 AM

Hi All,

I have a list containing numerous records. There are many fields, One of the field in the record is "CODE" there are different codes in this field, all i want to do is fetch only specific field of the record pertaining to CODE = 105, 106 and 109, on a separate sheet, i want to have a macro so that i can fetch the records, it should search all the code one by one until the Code list ends.

Thanks in advance

**Richard Buttrey** · 12-23-2008, 05:56 AM

Hi,

Why do you need a macro?
You can achieve this with the standard Data Filtering functionality of Excel.
If your unfamiliar with this, check out the Excel Help on the subject. If you still have problems, post back.

Rgds

**broro183** · 12-23-2008, 06:41 AM

To add to Richard's comment,

With 3 (or more) criteria it can be easier to add a "helper column" & perform the filtering on that column before copying & pasting the visible rows to the result sheet.

An example of a helper formula for this example would be to create a new "Reference Table" sheet (or section on current sheet) with the list of codes to filter for & then enter the below formula into column I.

Please Login or Register  to view this content.

hth
Rob

**Amarjeet Singh** · 12-23-2008, 07:59 AM

But how i can i avoid unwanted columns then? i dont want to manually delete the columns as there are many columns , is it possible to fetch the data based on column heading?

thanks

**Richard Buttrey** · 12-23-2008, 08:50 AM

Hi,

See the attached workbook.

I have added three names. "Data" which covers your original data in A1:H22 of the Problem sheet, "DataOut" which is A1:E1 of the results sheet, and "Criteria" which is G1:G4 of the results sheet.

Now erase the filtered data in A2:E13 of the results sheet so that we can demonstrate the filtering.

From the Menu choose Data Filter Advanced Filter. In the 'List Range' box enter the name 'Data', in the 'Criteria Range' box enter 'Criteria' and in the Copy To box enter 'DataOut' then press OK.

HTH

**Amarjeet Singh** · 12-29-2008, 04:16 AM

Great ! its working one more thing is it possible to make the "DATA" Input range dynamic, so that it can adjust itself according to the range of Input data.

Thanks once again.

**arthurbr** · 12-29-2008, 04:48 AM

Go to Insert - Name - Define
Click on Data
In the "Refers To" box insert : =OFFSET(Problem!$A$1;0;0;COUNTA(Problem!$A:$A);8) instead of the existing range