MrExcel.com - Your One Stop for Excel Tips & Solutions

extract info from table via array, not pivot table

Posted by Brad on February 07, 2001 2:30 PM

Hello;

I have a table of date sensitive data

col 1 col 2 col 3 col 4
date number number number
date number number number
date number number number
date number number number
date number number number
etc ( approx 700 lines )

I need to extract the whole row of data for criteria that match col 2 number to a new worksheet. I.E. column 2 is the amount of old mortgage and I want to "boil down" my list from 700 to 20-30.

Help searchs want me to use a pivot table, but I need the info to be dynamic ( use an array? ) so that I can set up 2 or 3 worksheets with various column 2 numbers. I don't want to count the row, I want to transfer all row\column data to a new worksheet without blank rows inbetween my matches.

Any help appreciated.

thanks...Brad

Posted by Dave Hawley on February 07, 2001 8:35 PM

Hi Brad

A Pivot Table would be the best method. They can also be dynamic. To achieve this you need to create a Dynamic Named Range (very handy). I have some examples of these on my web page, under the link "Dynamic Named Ranges". If you have any problems at all email me at my above address and CC in: dhawley@micl.com.au

If you like I could whip up a working example and send it to you ?


Dave


OzGrid Business Applications

Posted by b on February 08, 2001 7:35 AM

Posted by Brad on February 08, 2001 7:41 AM

Dave;

Thanks for the response to my request. I did check out the dynamic name ranges on your website. That is something that I didn't know about.
I didn't see an example of a dynamic range that filtered the information that matched. I.E. if column A has 500 records and 15 of them are the same number, how do you set the range (refer to formula)to show the 15 matches in the new worksheet? ( without zeros and or blank rows inbetween?)

Thanks in advance for any help to solving this problem.

Brad

Posted by Dave Hawley on February 08, 2001 1:23 PM


HI Brad

the dynamic range is used as a named range for your Pivot Table. It is the Pivot Table itself that will filter all your data to your needs.


Dave
OzGrid Business Applications