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

[j33pguy]

hi,
i have a very important question to ask:

i have a huge table with a lot of columns......
one of the columns has certain identifiers such as lets say A, B, C, D, etc.

now what i need is for the user to be able to have a list of these "identifiers" (i have already created the list), and based on the selection of the user, all of the rows of that certain identifier would show:

here is an example:



if the identifier A has five rows....meaning that A is in five consecutive rows; if the user chooses "A" then i want all of the information from those FIVE rows to show...

does that make sense?

 

[j33pguy]

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

Aladin, everyone,
Thank you so much for everything....
you all are great!!!!!!!!!



your code worked beautifully Aladin! Bravo!

 

[djl0525]

I needed to do this recently. I almost understand what's being done here.

Aladin, could you help me out with a few plain English translations of some of the formulas, please?

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

[G1] =LOOKUP(9.99999999999999E+307,F:F)

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

Thank you,
DJ

 

[Aladin Akyurek]

I needed to do this recently. I almost understand what's being done here.

Aladin, could you help me out with a few plain English translations of some of the formulas, please?

OK.

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

Evaluates each record in A:B in turn and assigns a sequential index number to each record if it meets the condition specified by

(A2<>"")*(B2=$E$1)

that A is not blank and B is equal to the value in E1. The LOOKUP bit

LOOKUP(9.99999999999999E+307,$F$1:F1)+1

does the assignment by adding one to last index number it looks up in the range of $F$1:F1. Note that this range grows with each record that is examined. Similar to the formula in G1, this LOOKUP expression finds the last value in the range of interest by binary search. The big number is improbable to occur in the data under consideration. When binary search hits the last used numerical cell (it does so very fast), the numerical value in the last used cell will be smaller or equal to 9.9..., a condition that makes LOOKUP return that numerical value.

[G1] =LOOKUP(9.99999999999999E+307,F:F)

See above. The value this formula returns is the number of hits, that is, the number of records in A:B which satisfy the stipulated conditions.

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

Note that the ROW()-ROW(H$2)+1 bit, copied down, creates a series with a step value of 1. H2 --> 1, H3 --> 2, H4 --> 3, etc. This formula match each value from the series against F-range in order to calculate the position of that value within the F-range. INDEX returns the corresponding value from A-range at the calculated position.

 

[djl0525]

Thanks. I will print this and study it.

DJ

 

[djl0525]

It's taken me a while, but I have finally gotten around to studying this thread. I applied what I learned here to a file I downloaded from Chip Pearson's site and now I am able to see what used to be a list of dates and class names (imported from Oracle) in a calendar layout created by Chip Pearson! I can enter an Instructor's name in G2 and view that person's calendar on another sheet -- in a 5 week calendar layout!!

I have been wanting to do convert this list to a calendar for over a year! If anybody would like to have a copy of the file, just say so and I will PM it to you.

You guys (and gals) are awesome!
DJ