Urgent question: Looking up a certain value from a list....

T

Travis

Well-known Member

Joined

Feb 26, 2002

Messages

1,711

• Nov 4, 2004

• #2

Re: Urgent question: Looking up a certain value from a list

can you use Data>>Filter>>AutoFiler from the menu. Then if you select A from the dropdown you should get all rows with A

By default it will put a filter on every column. If you just hightlight the column(s) you want to filter on, then select if from the menu it will only put a filter on the selected column(s)

 

Upvote 0

J

j33pguy

Well-known Member

Joined

Aug 6, 2004

Messages

633

• Nov 4, 2004

• #3

Re: Urgent question: Looking up a certain value from a list

that is what i thought of first too but that's not gonna work for me since that creates many other problems....
is there anyway this could be done with a formula?

 

Upvote 0

J

j33pguy

Well-known Member

Joined

Aug 6, 2004

Messages

633

• Nov 4, 2004

• #4

Re: Urgent question: Looking up a certain value from a list

and the reason i can't do that is because that feature of EXCEL has a row limit of like 4800 rows or 4800 "identifiers" and i have close to 6500!

i can't believe EXCEL has that stupid ristriction!!!!
anyway around it?

 

Upvote 0

T

Travis

Well-known Member

Joined

Feb 26, 2002

Messages

1,711

• Nov 4, 2004

• #5

Re: Urgent question: Looking up a certain value from a list

There is no row limit restriction on that feature. But the drop down will only show 1000 results, is that what your refering to?

You have over 1000 possible selections?

 

Upvote 0

J

j33pguy

Well-known Member

Joined

Aug 6, 2004

Messages

633

• Nov 4, 2004

• #6

Re: Urgent question: Looking up a certain value from a list

please help if you know the answer...

 

Upvote 0

J

j33pguy

Well-known Member

Joined

Aug 6, 2004

Messages

633

• Nov 4, 2004

• #7

Re: Urgent question: Looking up a certain value from a list

i don't really know how to answer that question.....

but here is again what i need:

i need to display all of A rows....or all of C rows.....or all of CC rows....according to the user selection....

 

Upvote 0

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,562

Office Version

1. 365

Platform

1. Windows

• Nov 4, 2004

• #8

Re: Urgent question: Looking up a certain value from a list

Assuming that Columns A through D contain your data, Column B contains your "identifiers", and your data starts on the second row...

E1: enter the "identifier" of interest

F1: enter a 0 (zero)

F2, copied down:

=IF((A2<>"")*(B2=$E$1),LOOKUP(9.99999999999999E+307,$F$1:F1)+1,"")

G1:

=LOOKUP(9.99999999999999E+307,F:F)

H2, copied across and down:

=IF(ROW()-ROW(H$2)+1<=$G$1,INDEX(A$2:A$10,MATCH(ROW()-ROW(H$2)+1,$F$2:$F$10,0)),"")

Hope this helps!

 

Upvote 0

T

Travis

Well-known Member

Joined

Feb 26, 2002

Messages

1,711

• Nov 4, 2004

• #9

Re: Urgent question: Looking up a certain value from a list

j33pguy said:

i need to display all of A rows....or all of C rows.....or all of CC rows....according to the user selection....

Click to expand...

How many choices (A, C, CC) does the user have to pick from?
If its less than 1000 you should be able to AutoFilter

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Nov 4, 2004

• #10

Re: Urgent question: Looking up a certain value from a list

j33pguy said:

that is what i thought of first too but that's not gonna work for me since that creates many other problems....
is there anyway this could be done with a formula?

Click to expand...

Book2
	A	B	C	D	E	F	G	H	I
1				A
2				0		4	A
3	X	Y	Z	Count		Pos	X	Y	Z
4	A	y210	z4334	1		1	A	y210	z4334
5	A	y539	z4439	2		2	A	y539	z4439
6	B	y177	z5154			4	A	y115	z2042
7	A	y115	z2042	3		7	A	y855	z9268
8	B	y490	z5168
9	C	y622	z1464
10	A	y855	z9268	4
11	C	y779	z4405
12
Sheet1

Formulas...

D1:

=G2

Reads the data validation cell.

D2 must house a 0.

D4, copied down:

=IF(A4=$D$1,LOOKUP(9.99999999999999E+307,$D$2:D3)+1,"")

F2:

=LOOKUP(9.99999999999999E+307,$D$4:$D$11)

F4, copied down:

=IF(ROW()-ROW(F$4)+1<=$F$2,MATCH(ROW()-ROW(F$4)+1,$D$4:$D$11),"")

G2 houses a dropdown list.

G4, copied across to I4 then down:

=IF(N($F4),INDEX(A$4:A$11,$F4),"")

 

Upvote 0