Multiple occurrences

[nesthead98]

I have a long worksheet that has this sort of structure (note, this isn't actual code - I'm using code boxes so the columns line up properly):

Please Login or Register  to view this content.

So, I can have multiple occurrences of the same name (the Name column is alphabetized), and the other info has no order, and has a wide variety of possible info (including nothing).
On another sheet, I have an input cell (ex. "A"). And below that cell, I have the name, and info cells returned, so the return sheet might look like this:

Please Login or Register  to view this content.

I can make this work fine using VLookup, but I can't get it to return all of the values for each instance. So, this is what I would like the worksheet to show if the input value is "B".

Please Login or Register  to view this content.

But, it only shows the first instance.
Any ideas?

[mikerickson]

Have you looked at AdvancedFilter?
When using the Copy to other location option, the destination sheet should be active when AdvancedFilter is pressed otherwise a misleading error message appears.

[nesthead98]

I'm not using any copy and pasting; all I want is the return sheet to change depending on what I type in the input cell. The sheet with all of the info is usually too long for me to bother searching for the info.

[mikerickson]

The formula solution is much more resource intesive than AdvancedFiler, but if the "price" is worth it you could name your data list DataList
Put the name in Sheet2!A1
The headers in Sheet2 row 2

Put this CSE formula in Sheet2! A3

=INDEX(Sheet1!A:A,SMALL(IF(INDEX(DataList,,1)=$A$1,ROW(DataList),ROWS(DataList)+20),ROW(A1)),1)&""

and drag it down and to the right.

This formula needs to be confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)

[nesthead98]

I don't know what a CSE formula is, and what does "confirmed" mean in Excel jargon?
I tried just typing in exactly what you wrote, and following your instructions, but it produces a #VALID! error.
Also, when I highlight any of the cells in your example, "DataList" never appears in the top-right corner.
I don't mind making a Macro to solve the problem, if that's what Advanced Filter will do.

[nesthead98]

Alright, I solved it.
It's probably not as efficient as possible, so I'm going to leave this thread as unsolved.
I created a cell that uses the MATCH function to find the last occurrence of the input value (so, for B, it would return 3). Then, I used the COUNTIF function to count how many times the input value appears (for B, 2).
Then, in the return the info and stuff section, I used the INDEX function with the data chart as the array, the last position minus the number of occurrences plus an appropriate number (depends on the row number) as the row, and the appropriate column number as the column.
It's long and complicated, and is extremely specific, so I am still looking for a better method. Help is still welcome.

[Palmetto]

I don't mind making a Macro to solve the problem, if that's what Advanced Filter will do.

Advanced Filter automated with a bot of code is the ticket here. See attached. Entering a valid entry into cell B5 on sheet2 will trigger the code to update the results.

Example Code:

Please Login or Register  to view this content.

[nesthead98]

Woah.
I like it, but I have no idea how it works.
Could you explain each of the lines of code? I'm going to use it, I just want to figure out how it works.
Also, does it work if each letter has a different number of occurrences?
Many props to you, Palmetto.