Lookups

Originally Posted by rolltyde12

I need to be able to lookup information on a specific sheet and have every instance of a specific word be placed in a column on another sheet.

So, if I have a ton of data on Sheet 1, I want to be able to have a heading on Sheet 2 that says "Ace", and have a function that looks for the word "Ace" on Sheet 1, and places every instance of this word under the heading "Ace" on Sheet 2.

So on Sheet 1:
Ace 34
Blue 32
Ace 99
Green 34

On Sheet 2, you would see:

ACE
Ace 34
Ace 99


any help?

see the attached file.

Starguy, thanks for the response. I am still unsure how the helper column plays into the equation. I realize the function pulls the information that shows "a". Can you go into more detail? When I change to lookup value to b or c, nothing happens.

Originally Posted by rolltyde12

Starguy, thanks for the response. I am still unsure how the helper column plays into the equation. I realize the function pulls the information that shows "a". Can you go into more detail? When I change to lookup value to b or c, nothing happens.

be sure that calculation is set to Automatic
before changing value
go to Tools > Options > Calculation tab > and check Automatic
then change value and see how it grabs all values.

Originally Posted by rolltyde12

Hi Starguy, I am attaching a file for you to look at. I need the function to be placed under Ace on the Formation Sheet. I want it to lookup every occurance of the letters "Ac" on the Master Sheet in Column B. Whenever a call has those letters, that cell will be added to Column A on the Formation Sheet. Can you help?

col B in Master sheet is helper. it looks in col C for value "Ac" and if cell in col C contains "Ac" in it, it will assign an incremental number to it in col B.

see the file.

Originally Posted by rolltyde12

Okay, this is awesome stuff, but I have one more question. In Formation sheet, If I want it to do the same thing in another column, looking for instances of "Bu", can I do that? Will I need more helper columns?

Thanks.

yes you need more helper columns in Master sheet but you can hide them all if you dont want them to see.

see attached file (col B & C are hidden in Master sheet)

Originally Posted by rolltyde12

When I insert more helper columns, everything on my formations page goes blank. Why is this. It's like it doesn't recognize that a column has been inserted.

yes it will happen so because coloumn references would be changed after inserting new column to Master sheet.
what you have to do is to decide first that how much helper columns you need at maximum (i-e how much values you want to search and return in Formation sheet.)
then implement formula in helper columns and Formation sheet columns.
you can tell me that how many values you want to search in Master and how many columns you need in Formation tab. then I'll make it for you.