Extract list using functions

E

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
 
F

Frank Kabel

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
 
A

Aladin Akyurek

Let A2:D4 on Sheet1 house the sample you provided.

Sheet1 (Source)

In E1 enter: 0
In E2 enter: Count
In E3 enter & copy down:

=IF((A3<>"")*(D3<12),LOOKUP(9.99999999999999E+307,$E$1:E2)+1,"")

Sheet2 (Destination)

In A1 enter:

=LOOKUP(9.99999999999999E+307,Sheet1!E3:E5)

In A2 enter: Pos (Abbreviated from Position)
In A3 enter & copy down:

=IF(ROW()-ROW(A$3)+1<=$A$1,MATCH(ROW()-ROW(A$3)+1,Sheet1!$E$3:$E$5),"")

In B3 enter & copy across to E3 then down:

=IF(N($A3),INDEX(Sheet1!A$3:A$5,$A3),"")
 
E

Elijah

Hi Frank,

How about just extracting the customers name - then I could use a standard
vlookup to extract all other details. Would I need to use a similar formula?

Elijah
 
F

Frank Kabel

Hi
the first formula just does this. extracts all customer names which fullfill
your criteria. VLOOKUP then could be a little bit faster but again: This kind
of array formulas are getting slow for >300 records
 
F

Frank Kabel

Hi Aladin
I like your approach (and it probably much faster than my one-cell solution).
Just some curious questions

[...]
=IF((A3<>"")*(D3<12),LOOKUP(9.99999999999999E+307,$E$1:E2)+1,"")
Click to expand...

I would assume that the following is faster (not tested though):
In A1 enter:
=LOOKUP(9.99999999999999E+307,Sheet1!E3:E5)
Click to expand...
If I got your description right woulnd't
=MAX(Sheet1!E3:E5)
return the same?

As said: Not tested from my side - just curious :))
Frank
 
E

Elijah

Thanks Frank, Aladin

Just tried your function and it worked. I don't have a very large list about
200 rows so it wasn't a problem.

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

Elijah
 
D

Domenic

Elijah said:
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?
Click to expand...

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
 
E

Elijah

Domenic said:
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!
Click to expand...

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.
 
Top