Copy filter Information

G

Guest

I am working in excel 2003. I have a list of data that I need to filter in
column A, B, C, then I want it to automatically copy the results of D, E, F,
G to D1, E1, F1, G1.

Example:
Manufacture
A1 B1 C1 D1
0 0 0 0


Manufacture Type Size A1 B1 C1 D1
Automax ISOF04 B050 0.585 0.585 0.2187 1.25
Automax ISOF05 B063 0.696 0.696 0.2813 1.50
Automax ISOF07 B085/B100 0.974 0.974 0.3437 2.25
Automax ISOF10 B115/B125 1.42 1.42 0.4063 3.25
Automax ISOF12 B150 1.74 1.74 0.5312 4.00
Automax ISOF14 B175/B200 1.949 1.949 0.6563 4.375

Result I need:

Manufacture
A1 B1 C1 D1
0.974 0.974 0.3437 2.25


Manufacture Type Size A1 B1 C1 D1
Automax ISOF07 B085/B100 0.974 0.974 0.3437 2.25

Can any one help????
 
D

Dave Peterson

So you duplicate the criteria in A1:C1, then filter a range using those values.

Say that range is in A3:G999 (headers in row 2), you don't need to use filtering
to get those values. You can use formulas.

I'd put this in H1 (a helper cell)

=match(1,((a3:a999=$a$1)*(b3:b999=$b$1)*(c3:c999=$c$1)),0)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

and format H1 using a custom format of: ;;;
(;;; will make that cell look empty).
(Or hide column H)

Then in D1, put this formula:
=if(iserror(h1),"",index(d3:d999,h1))

in E1:
=if(iserror(h1),"",index(e3:e999,h1))

In F1:
=if(iserror(h1),"",index(f3:f999,h1))

In g1:
=if(iserror(h1),"",index(g3:g999,h1))


You can u
 
G

Guest

Thank you so much....That works great!!!!!!!!!

Dave Peterson said:
So you duplicate the criteria in A1:C1, then filter a range using those values.

Say that range is in A3:G999 (headers in row 2), you don't need to use filtering
to get those values. You can use formulas.

I'd put this in H1 (a helper cell)

=match(1,((a3:a999=$a$1)*(b3:b999=$b$1)*(c3:c999=$c$1)),0)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

and format H1 using a custom format of: ;;;
(;;; will make that cell look empty).
(Or hide column H)

Then in D1, put this formula:
=if(iserror(h1),"",index(d3:d999,h1))

in E1:
=if(iserror(h1),"",index(e3:e999,h1))

In F1:
=if(iserror(h1),"",index(f3:f999,h1))

In g1:
=if(iserror(h1),"",index(g3:g999,h1))


You can u
Click to expand...
 
Top