Extract list using functions

[Elijah]

Hi, can anyone help with the following:

I want to extract items in a list and show them in another worksheet. An
example of the master list would be:

Name Dollars Quantity Months
Customer1 $ Q 12
Customer2 $ Q 12
Customer3 $ Q 6

I want to be able to extract all those customers which have less than
12months data (as indicated in the month column). I want to be able to do
this using functions in another worksheet and list those seperately there.

Is there a way to do this?

Elijah

 

[Guest]

Hi
you could use formulas but with lots of data (<300 recors) this gets quite
slow and I would suggest to use 'Data - Filter' instead.

But here we go for one formula option (all formulas are antered as array
formulas with CTRL+sHIFT+ENTER)
=INDEX('sheet1'$A$1:$A$100,SMALL(IF('sheet1'!$C$1:$C$100<12,ROW('sheet1'!$C$1:$C$100)),ROW(1:1)))
and copy this down as far as needed

 

[Domenic]

Out of interest - could you tell me how modify the function if I were t
use
another criteria - say to pickup all customers which show 6 months?

Just change the <12 bit to =6 ...

=INDEX('sheet1'$A$1:$A$100,SMALL(IF('sheet1'!$C$1:
$C$100=6,ROW('sheet1'!$C$1:$C$100)),ROW(1:1)))

Hope this helps

 

[Elijah]

Just change the <12 bit to =6 ...

=INDEX('sheet1'$A$1:$A$100,SMALL(IF('sheet1'!$C$1:
$C$100=6,ROW('sheet1'!$C$1:$C$100)),ROW(1:1)))

Hope this helps!

Thanks..Previously when I tried this it didn't work - now when I enter
it - it picks the customer up. Must be the good ol Ctrl-Shift-Enter
thing.