shorten list formula

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Sep 20, 2005

• #2

hi eric,

you should be able to modify this approach:

http://www.mrexcel.com/board2/viewtopic.php?t=158655

...like an example?

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Sep 20, 2005

• #3

gave you one anyway:

Book1
	A	B	C	D	E
1
2	NAMES	Flag			List
3	John	1		1	John
4	Pete	0		2	Marc
5	Matt	0		3	Luke
6	Marc	1		4	Brian
7	Luke	1		5	Aladin
8	Brian	1		6
9	Geoff	0		7
10	Aladin	1		8
11
Sheet1

formula is:

=IF(D3<=COUNTIF($B$3:$B$10,1),INDEX($A$3:$A$10,SMALL(IF($B$3:$B$10=1,ROW($B$3:$B$10)-ROW($B$3)+1,ROW($B$10)+1),D3)),"")

...array entered with control + shift + enter, not just enter.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Sep 20, 2005

• #4

erik.van.geit said:

Hello, formula-gurus,

This is a bit over my head.
The table explains itself, I think.

Code:

NAMES        RESULT
John    1    John
Pete    0    Marc
Matt    0    Luke
Marc    1    Brian
Luke    1    Aladin
Brian   1	
Geoff   0	
Aladin  1

is it possible to generate a shortened list with all names followed by a "1" ?

kind regards,
Erik

Click to expand...

Two methods: One I have been posting for the purpose you want and its many variations and another which gets posted on worksheet.functions, often in a non-robust form. The first is fast, the second is slow and is only needed under specific conditions...

Book3
	A	B	C	D	E	F	G	H	I
1			0		Cond:	1	5		5
2	NAMES	FLAG	IDX				RESULT		RESULT
3	John	1	1				John		John
4	Pete	0					Marc		Marc
5	Matt	0					Luke		Luke
6	Marc	1	2				Brian		Brian
7	Luke	1	3				Aladin		Aladin
8	Brian	1	4
9	Geoff	0
10	Aladin	1	5
11
Sheet1

Method 1

C1 must house a 0.

C3, copied down:

=IF(B3<>"",IF(B3=$F$1,LOOKUP(9.99999999999999E+307,$C$1:C2)+1,""),"")

F1 specifies the condition.

G1:

=LOOKUP(9.99999999999999E+307,C1:C10)

calculates the number of items that meet the flag condition.

G3, copied down:

=IF(ROW()-ROW($G$3)+1<=$G$1,LOOKUP(ROW()-ROW($G$3)+1,$C$3:$C$10,$A$3:$A$10),"")

yields the desired result list.

Method 2

I1:

=SUM(B3:B10)

I3:

=IF(ROW()-ROW($I$3)+1<=$I$1,INDEX($A$3:$A$10,SMALL(IF($B$3:$B$10=$F$1,ROW($B$3:$B$10)-ROW($B$3)+1),ROW()-ROW($I$3)+1)),"")

which is confirmed with control+shift+enter then copied down yields the desired result list.

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Sep 20, 2005

• #5

PaddyD,
thank you for reponse

I'm trying but don't see the link between Chips page (I already saw before) and this problem. Perhaps I'm blind now, where should I look?

...like an example?

Click to expand...

do you mean you would provide an example ?
then my answer would be: "YES, please"
kind regards,
Erik

 

Upvote 0

K

Krishnakumar

Well-known Member

Joined

Feb 28, 2003

Messages

2,615

• Sep 21, 2005

• #6

Hi Erik,

Use Advanced Filter.

Have a look..

Book1
	A	B	C	D	E	F	G
1	NAMES	FLAG				NAMES	FLAG
2	John	1		TRUE		John	1
3	Pete	0				Marc	1
4	Matt	0				Luke	1
5	Marc	1				Brian	1
6	Luke	1				Aladin	1
7	Brian	1		List Range	A1:B9
8	Geoff	0		Criteria Range	D1:D2
9	Aladin	1		Copy to:	F1
Sheet2

Formula in D2,

=B2=1

HTH

 

Upvote 0

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Sep 21, 2005

• #7

Thanks, Paddy and Aladin!
We posted almost at the same time.

intresting to study how things work not only concerning acurateness but also speed

Thank you, Krishnakumar,
I really need a formula for a "permanent" solution.
You provided a clear example of the use of advanced filter.

kind regards to you all!
Erik

 

Upvote 0

J

Jorinho

New Member

Joined

Mar 10, 2005

Messages

18

• Sep 21, 2005

• #8

Hello All!!!

I think I need a "little" help from you in my post.
Please come and see.

http://www.mrexcel.com/board2/viewtopic.php?p=816064

Thanks in advance

Best Regards

Jorinho

 

Upvote 0

A

acw

MrExcel MVP

Joined

Feb 13, 2004

Messages

4,814

• Sep 22, 2005

• #9

Erik

Assuming that your data is in the range A1:B9 then put a space in B10.

In C2 array enter

Code:

=INDEX($A$1:$A$10,SMALL(IF($B$2:$B$9=1,ROW($B$2:$B$9),10),ROW()-1))

and copy down.


Tony

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Sep 22, 2005

• #10

acw said:

Erik

Assuming that your data is in the range A1:B9 then put a space in B10.

In C2 array enter

Code:

=INDEX($A$1:$A$10,SMALL(IF($B$2:$B$9=1,ROW($B$2:$B$9),10),ROW()-1))

and copy down.


Tony

Click to expand...

That's a non-robust form of the array method (Method 2) I was talking about.

 

Upvote 0